Worksheets in Loop



This is probably simple, but here goes anyway...

I have 7 worksheets with pivot tables and an 8th sheet with detail data.
I've put together code (that works) to define a named range based on the
detail data. I would now like to loop through the other 7 sheets and update
each pivot table (already set to that named range).

Currently, I have simple code that goes to each tab individually, but
looking ahead, I would like to put the code into a loop. Here is what I have
so far...

Dim T1, T2, T3, T4, T5, T6, T7 As String

T1 = "abc"
T2 = "def"
T3 = "ghi"
T4 = "jkl"
T5 = "mno"
T6 = "pqr"
T7 = "stu"

....the loop that I tried (unsuccessfully) looks like...

For x = 1 to 7
Worksheets("T" & x).Activate
Set pvtTable = Worksheets("T" & x).Range("A3").PivotTable
Next x

....the problem is with the <Worksheets("T" & x)> lines - not sure what the
correct syntax would be (I've tried several different ways). Maybe use code
to simply go to the next visible sheet?

Thanks in advance for the help.


Thank you. Makes sense.

Mike said:
Sub test()
Dim WS_Count As Integer
Dim WS_Name As Worksheet
Dim i As Integer
WS_Count = ThisWorkbook.Worksheets.Count

For i = 1 To WS_Count
MsgBox Worksheets(i).Name
Next i
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question