Dynamically reference worksheet code name

[Guest]

I'd like to do something like the following

For i = 1 to 10
Set aws = "Test" & i 'Test1-test10 are worksheet code names
'perform actions on aws
next i

How do I do this?

Thanks,
Barb Reinhardt

 

[Guest]

Sounds like you're wanting to loop through sheets Test1 through Test 10.
Post back if that's not it. As an example, the following code will perform
an action on Sheets Test1-Test10 by placing "Hello, World" in cell A1.

Dim sht as Worksheet
Dim i as integer
For i=1 to 10
set sht = Worksheets("Test" & i)
sht.Range("A1").Value = "Hello, World"
Next i
set sht=Nothing

 

[Guest]

Here is a solution that I have used. It is a little different from what you
asked but it works...

Sub test()
Dim wks As Worksheet

For Each wks In Worksheets
If Left(wks.CodeName, 4) = "Test" Then MsgBox wks.CodeName
Next wks
End Sub

 

[Guest]

Thanks. Why didn't I think of that?

Sounds like you're wanting to loop through sheets Test1 through Test 10.
Post back if that's not it. As an example, the following code will perform
an action on Sheets Test1-Test10 by placing "Hello, World" in cell A1.

Dim sht as Worksheet
Dim i as integer
For i=1 to 10
set sht = Worksheets("Test" & i)
sht.Range("A1").Value = "Hello, World"
Next i
set sht=Nothing

--

Hope that helps.

Vergel Adriano

 

[Guest]

Actually, that's more like what I was looking for. Thanks.

 

[Guest]

I don't think that works for the sheets code name.