Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 pvtTable.RefreshTable 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets in Loop
Thank you. Makes sense.
"Mike" wrote: 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 "ReportSmith" wrote: 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 pvtTable.RefreshTable 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop thru worksheets(i) | Excel Programming | |||
For/Next Loop through worksheets | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming |