Navigate to Sheet name from cell Value

[uaku]

hi,
I have all the sheet names in range from A2 to A32. I want to navigate to each sheet get some value and paste it on sheet called "GetNames"
message box is displaying all the cell values but its not going to the specific sheet. what am i doing wrong?

Sub average()
Range("A2").Select
For Each CVal In Sheets("GetNames").Range("A2:A32")

sheetname = CVal.Value
MsgBox sheetname
Worksheets(sheetname).Activate


Next CVal
Sheets("GetNames").Activate
End Sub

[Bob Phillips]

Works for me, what happens there?

[D_Marcel]

uaku, if you want to get values from each Worksheet and paste it in the "GetNames, the instruction "Sheets("GetNames").Activate" should be before of "Next Cval", if I understand your propose. I tested this way and it worked for me:

Sub average()
Range("A2").Select
For Each CVal In Sheets("GetNames").Range("A2:A32")
On Error GoTo point1
sheetname = CVal.Value
MsgBox sheetname
Worksheets(sheetname).Activate
Sheets("GetNames").Activate
Next CVal
point1:
Sheets("GetNames").Activate

End Sub

When the last Sheet is reached, the MsgBox displays nothing and back to GetNames because there is no a next Sheet to select. In this case you can put a If inside the For Each Next loop to display some alert in this MsgBox.

Hope I have helped.
Douglas Marcel

[uaku]

I get error here " Worksheets(sheetname).Activate"
It does not go this sheetname variable. if you think its going and coming back to "GetNames" thats because you put a pointer saying on error come to this sheet thats where its ending up. take the pointer and go through this, it should wait on the last CVal value sheet. which it does not.
The error says "Run time error '9' script out of range "

I have around 18 sheets in a file, however that can be variable. So I read all the sheet names and put in one sheet. then I read the range using Sheets("GetNames").Range("A2:A32").
Now I want to loop through these sheets to pick up certain values and bring it to this sheet and paste here and do some maths after that. Thats what I am trying to accomplish. Just want to add one thing my sheet names are numbers ranging from -9 to 0 to 9. Thats how I got the datafile so no choice on why its that way

[D_Marcel]

uaku, let me see your Workbook. Can you, please, share with us? I want to test it in my PC. :think:

Thanks!
Douglas Marcel

[uaku]

uaku, let me see your Workbook. Can you, please, share with us? I want to test it in my PC. :think:

Thanks!
Douglas Marcel

I attached a file

[D_Marcel]

uaku, I tested your code setting the variant shetname as String and worked very well, take a look:

Sub average()
Dim sheetname As String
Range("A2").Select
For Each CVal In Sheets("GetNames").Range("A2:A32")

sheetname = CVal.Value
MsgBox sheetname
Worksheets(sheetname).Select
Range("A1").Select

Next CVal
End Sub

Hope it can solve your problem. :thumb

Douglas Marcel