2

In Sheet1 there is, among other things, a list of students by Student ID and their Grade Level. In the Workbook there is also a separate Worksheet for each student. The Worksheets are named according to the Student ID. I need to copy the Grade Level for each student to their specific Worksheet. This must be done for all students.

For example Column AA contains the Student ID's, Column AB contains the Grade Levels for each student. I need to copy Student 12345, Grade 4 to Worksheet 12345 Cell F1. Then I need to move to the next student and do the same thing until I have no more students.

I have tried many methods, but I keep getting stuck. I've found examples that are close, but always miss one key thing to make it work so I'm hoping someone will be able to get me started. I feel it should be easy, but it hasn't proven to be.

Edit:

I was looking at trying to figure it out in steps. On a simple test file I tried: 

Dim I As Long 
For I = 1 To Sheets.Count
Worksheets(I).Activate 
Workbooks("StuData.xlsm").Sheets(I).Range("F1").Value = Workbooks("StuData.xlsm").Sheets("Sheet1").Cells(I, 2)
Next

Then I tried: 

Dim Sheetname as String 
Sub activateSheet(sheetname As String) 
    Worksheets(sheetname).Activate
End Sub
Degustaf
  • 2,655
  • 2
  • 16
  • 27
user3574547
  • 99
  • 1
  • 2
  • 8
  • You should post the code that you have now so that people can see what might be going wrong. – Degustaf Sep 23 '14 at 14:17
  • I was looking at trying to figure it out in steps. On a simple test file I tried: Dim I As Long For I = 1 To Sheets.Count Worksheets(I).Activate Workbooks("StuData.xlsm").Sheets(I).Range("F1").Value = Workbooks("StuData.xlsm").Sheets("Sheet1").Cells(I, 2) Next Then I tried: Dim Sheetname as String Sub activateSheet(sheetname As String) Worksheets(sheetname).Activate End Sub – user3574547 Sep 23 '14 at 16:16

2 Answers2

1

You don't need VBA to do this.

First create the following Named Formula (Ctrl+F3->New) with SheetName in the Name: field scoped to the workbook. Enter the following exactly as it is below in Refers to: and click OK:

=RIGHT(CELL("FILENAME",!$A$1),LEN(CELL("FILENAME",!$A$1))-FIND("]",CELL("FILENAME",!$A$1),1))

Be sure to include the extra ! at the beginning of each cell address! Very important. Quick explanation: although is it scoped to the entire workbook, the ! at the beginning of the cell addresses makes each cell address in the named formula be evaluated in the context of the current worksheet. It is equivalent to having a different Sheetname variable for each worksheet (scoped to each worksheet).

Now select the first student sheet. Press Ctrl+Shift+PgDn and repeat, or simply hold Ctrl and click each individual student sheet, until all the student sheets are selected. You are now editing all of the student sheets at the same time.

In cell F1 of one of the student sheets (doesn't matter which), enter the following:

=INDEX('ALL STUDENTS SHEET'!$AB:$AB,MATCH(VALUE(SheetName),'ALL STUDENTS SHEET'!$AA:$AA,0))

(Of course you'll need to replace ALL STUDENTS SHEET with the name of the first sheet.)

Finally, deselect the multiple sheets by selecting the first sheet so you can continue working without editing all the sheets at the same time.

EDIT: Note that in order for SheetName to work as expected, the workbook must have been saved to disk (i.e. it will not work on a new workbook until it is saved since the CELL("FILENAME",<Cell Address>) formula needs a filename).

Rick
  • 43,029
  • 15
  • 76
  • 119
  • Okay, I tried this, but I get a formula error and this part of the Index formula is highlighted (not sure if it is a clue): '[Sheet1]'$AB – user3574547 Sep 23 '14 at 16:21
  • There were some typos (sorry). All fixed now. Was missing the last `!` in the sheet references and had an extra `$` somewhere. I've also tried to make it more clear; you do not use `[]`. Just put `Sheet1!$AB:$AB`, etc etc. – Rick Sep 23 '14 at 16:53
  • Getting closer. Now I get a #VALUE! error in the F1 cells. When I try error checking I get "A value used in the formula is of the wrong data type." I think what it doesn't like is SheetName in the MATCH section. In the first part where you have "FILENAME" I am supposed to put the actual file name, right? – user3574547 Sep 23 '14 at 22:55
  • Nope, you put "FILENAME". – Rick Sep 24 '14 at 12:09
  • Try stepping through the formula in `F1` (`Formulas`->`Evaluate Formula`->`Evaluate`) to find what calculation step is causing the error. – Rick Sep 24 '14 at 12:15
  • "The next evaluation will result in an error." --MATCH("123",Sheet1!$AA:$AA,0)) At least I'm getting an #N/A error. – user3574547 Sep 25 '14 at 00:07
  • The problem is the `MATCH()` function is trying to match the text "123" with the number 123. `Sheetname` needs to be wrapped in a `VALUE()` function to turn it into a number. Apologies; this was my oversight (didn't realize the student IDs were stored as numbers but I should have assumed). Will edit the answer. – Rick Sep 25 '14 at 14:42
  • Fantastic!!!! Works perfectly!! I can't thank you enough for being this persistent and helping me out. You have made 19 elementary school teachers very happy. One question: what does this part of the process actually do? =RIGHT(CELL("FILENAME",!$A$1),LEN(CElL("FILENAME",!$A$1))-FIND("]",CELL("FILENAME",!$A$1),1)) – user3574547 Sep 26 '14 at 22:57
  • Good, my wife is a teacher. You can thank me by marking my answer as the Accepted Answer. :) Well, `CELL("FILENAME",)` returns the full filename, including the computer directory, filename, sheetname, and address of the designated cell as a string. The `!` makes each cell address in the named formula be evaluated in the context of the current worksheet. The rest of the functions, `LEN()`, `RIGHT()`, `FIND()`, are functions used to work with strings (AKA text) in Excel. They are stripping out just the sheetname by itself. I suggest looking them up as they are very useful. – Rick Sep 27 '14 at 02:12
  • Here's more information about the `!` trick in named ranges: http://stackoverflow.com/questions/22920090/excel-vba-workbook-scoped-worksheet-dependent-named-formula-named-range-resul – Rick Sep 27 '14 at 02:13
1

Although I agree that what you want can be done without VBA, you can still try this:

Sub TransferGrades()
    Dim RID As Range, SID As Range, lrow As Long
    With Sheets("Sheet1") '~~> change to suit
        lrow = .Range("AA" & .Rows.Count).End(xlUp).Row
        Set RID = .Range("AA1", "AA" & lrow) '~~> change to suit
    End With
    For Each SID In RID
        On Error Resume Next
        '~~> You need to use CStr Function if ID's are numbers
        Sheets(CStr(SID.Value)).Range("F1").Value = SID.Offset(0, 1).Value
        If Err.Number <> 0 Then SID.AddComment "Not found,you need to add sheet." _
        Else SID.ClearComments
        On Error GoTo 0
    Next
End Sub

This will transfer grades to student ID sheets found only.
If the Student ID sheet is not found, it will be ignored and proceed with the next ID.
It will add comment on Student ID's that doesn't have it's corresponding Sheet. HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
  • Another terrific solution! I tried it and it worked perfectly. Now I have the task of trying to figure out what the code is doing so I can learn from it, as I am going to do with the first solution. Thank you very much for offering an alternative method!!! As a person who often gives homework, I now find that both of you have given ME homework! ;-) – user3574547 Sep 30 '14 at 11:32
  • This is a good answer, and an excellent set of VBA code to study and learn with. If you're interested in learning more VBA, here's one old answer of mine that you might find helpful. http://stackoverflow.com/questions/23689196/understanding-vba-code-using-to-and-step/23697899#23697899 – Rick Sep 30 '14 at 23:53