Dynamically reference worksheet code name

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Thanks. Why didn't I think of that?

Vergel Adriano said:
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
Click to expand...
 
Back
Top