Pivot table looping problem

[flipcube]

Hi All,

I have a workbook where I create several Pivot Tables (one tab for each). Each Pivot Table will be updated every fiscal month and each will be updated with the same information. I am having trouble coding a For Each Next Loop to update each pivot table. I used the macro recorder to retrieve the below code and tailored it as needed...works fine for the activesheet. How do I code the For Each Next loop to activate each sheet and update the Pivot Table...below is for January...I have similar code for each month.

Private Sub CommandButton9_Click()
'This Subroutine updates the Pivot Tables based on current fiscal month
Dim FiscalMonth As String
Dim Today As Date
Dim Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec As String
Dim lastyear As String
Dim currentyear As String
Dim nextyear As String

lastyear = Year(Date) - 1
currentyear = Year(Date)
nextyear = Year(Date) + 1

Today = Sheet1.Cells(1, 12)
If Today < Sheet2.Cells(4, 2) Then
FiscalMonth = "January"
'Removes previous year October
Oct = "Oct" & " " & lastyear
ActiveSheet.PivotTables("PivotTable1").PivotFields(Oct).Orientation = _xlHidden
'Adds fiscal year October
Oct = Format("Oct" & " " & "-" & " " & currentyear, "mmm-yy")
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Oct), "Oct" & " " & Year(Date), xlCount
Color_change
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dec" & " " & Year(Date))
.Function = xlSum
.NumberFormat = "#,##0"
End With


Thanks in advance

 

[flipcube]

Forgot to mention...

sheet1 contains my raw data and sheet2 my Fiscal calendar

 

[flipcube]

I solved the problem...here is my solution in case someone else has the same issue.

Private Sub CommandButton9_Click()
'This Subroutine updates the Pivot Tables based on current fiscal month
Dim FiscalMonth As String
Dim Today As Date
Dim month As String
Dim Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec As String
Dim lastyear As String
Dim currentyear As String
Dim nextyear As String
Dim sh As Worksheet
Dim pt As PivotTable

lastyear = Year(Date) - 1
currentyear = Year(Date)
nextyear = Year(Date) + 1

Today = Sheet45.Cells(1, 12)

If Today < Sheet46.Cells(4, 2) Then
FiscalMonth = "January"
For Each sh In Worksheets
For Each pt In sh.PivotTables
If sh.Name <> "RAW" Or sh.Name <> "Fiscal_Calendar" Then
sh.Activate
'Removes previous year October
Oct = "Oct" & " " & lastyear
sh.PivotTables("PivotTable1").PivotFields(Oct).Orientation = _
xlHidden
'Adds fiscal year October
Oct = Format("Oct" & " " & "-" & " " & currentyear, "mmm-yy")
sh.PivotTables("PivotTable1").AddDataField sh.PivotTables( _
"PivotTable1").PivotFields(Oct), "Oct" & " " & Year(Date), xlCount
Color_change
With sh.PivotTables("PivotTable1").PivotFields("Dec" & " " & Year(Date))
.Function = xlSum
.NumberFormat = "#,##0"
End With
End If
Next pt
Next sh

End sub