I currently have a file built that allows users to see
some calculations based upon what months they select from
a form. They can select up to 48 months (past 4 years of
data). Data is stored in 3 pivot tables and users see a
report produced from this data. Once users select a
number of months, they click on a button and the VBA
starts. The code loops through each one of the 48 months
to see if it has been selected, if it has, it
is 'visible' in the pivot table. The code loops through
all 48 months for the 3 pivot tables.

This is what I need help with. Once the button is clicked
and the code starts to run, it can take up to 25 minutes
to finish since the pivot table is refreshed each time a
month is hidden or unhidden. I've turned off the
automatic calculation, but that does not stop the pivot
table refreshing.

Any thoughts on how to stop the pivot tables from
refreshing until all of the months have looped through?

Thanks,

-Scott
---

Sheets("Pivot").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

Sheets("Pivot - Geo").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

Sheets("Pivot - Industry").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

Scott, if you try this at the beginning of your code it
may help

Application.ScreenUpdating = False

This stops the screen trying to continually refresh and
can produce some siginificant performance gains

-----Original Message-----

I currently have a file built that allows users to see
some calculations based upon what months they select from
a form. They can select up to 48 months (past 4 years of
data). Data is stored in 3 pivot tables and users see a
report produced from this data. Once users select a
number of months, they click on a button and the VBA
starts. The code loops through each one of the 48 months
to see if it has been selected, if it has, it
is 'visible' in the pivot table. The code loops through
all 48 months for the 3 pivot tables.

This is what I need help with. Once the button is clicked
and the code starts to run, it can take up to 25 minutes
to finish since the pivot table is refreshed each time a
month is hidden or unhidden. I've turned off the
automatic calculation, but that does not stop the pivot
table refreshing.

Any thoughts on how to stop the pivot tables from
refreshing until all of the months have looped through?

Thanks,

-Scott
---

Sheets("Pivot").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

Sheets("Pivot - Geo").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

Sheets("Pivot - Industry").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Fiscal Month")
For x = 0 To (MonthListSize - 1)
.PivotItems(MonthList(x, 0)).Visible = MonthList
(x, 1)
Next x
End With

.