So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The
problem comes when a user would deletes or even hides a tab or copy over a
pivot without renaming the table. Is there any way I can program it such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table names.

Thanks.

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The
problem comes when a user would deletes or even hides a tab or copy over a
pivot without renaming the table. Is there any way I can program it such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table
names.

Thanks.

Very nice! Simple and elegant.

Thanks!

"Roger Govier" wrote:

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The
problem comes when a user would deletes or even hides a tab or copy over a
pivot without renaming the table. Is there any way I can program it such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table
names.

Thanks.

This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is that I
have a Main Pivot table with calculations. I then have to group those
calculations together and so run a pivot of those. Since I can't do a pivot
on pivot, I have a formula that points to the results. The second pivot then
uses those formulas as its source.

With the solution below, those formulas doesn't have time to update when the
main pivot refreshes. Therefore, the second pivot still has the results from
the prior refresh. Any way around this?

"Roger Govier" wrote:

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a template.
However, I have it specifying the Sheet number and Pivot Table names. The
problem comes when a user would deletes or even hides a tab or copy over a
pivot without renaming the table. Is there any way I can program it such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table
names.

Thanks.

Hi Fred

If there is only 1 Pivot table on each of your sheets, you could use the
following code

Sub RefreshPivots()
Dim myArray As Variant, ws As Worksheet
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ws.PivotTables(1).PivotCache.Refresh
Next
End Sub

Change the Array of Sheets to the names of your sheets, in the order you
want them calculated.

If there is more than one Pivot Table on each sheet, then use the following
code

Sub RefreshPivots2()
Dim myArray As Variant, ws As Worksheet, pt As PivotTable
Dim i As Integer, ptcount As Integer
Dim ptname As String
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ptcount = ws.PivotTables.Count

For i = 1 To ptcount
ptname = ws.PivotTables(i).Name
ws.PivotTables(ptname).PivotCache.Refresh
Next i
Next
End Sub

The second macro will work with a single PT per sheet as well.
Again, change the array of sheet names to suit.
--

Regards
Roger Govier

"FredL" wrote in message
...
This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is
that I
have a Main Pivot table with calculations. I then have to group those
calculations together and so run a pivot of those. Since I can't do a
pivot
on pivot, I have a formula that points to the results. The second pivot
then
uses those formulas as its source.

With the solution below, those formulas doesn't have time to update when
the
main pivot refreshes. Therefore, the second pivot still has the results
from
the prior refresh. Any way around this?

"Roger Govier" wrote:

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a
template.
However, I have it specifying the Sheet number and Pivot Table names.
The
problem comes when a user would deletes or even hides a tab or copy
over a
pivot without renaming the table. Is there any way I can program it
such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table
names.

Thanks.

What if I refresh the main pivot, do a pause so that the formulas can catch
up, then refresh all; understandably, the main pivot will refresh again. Is
there a pause command?

"Roger Govier" wrote:

Hi Fred

If there is only 1 Pivot table on each of your sheets, you could use the
following code

Sub RefreshPivots()
Dim myArray As Variant, ws As Worksheet
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ws.PivotTables(1).PivotCache.Refresh
Next
End Sub

Change the Array of Sheets to the names of your sheets, in the order you
want them calculated.

If there is more than one Pivot Table on each sheet, then use the following
code

Sub RefreshPivots2()
Dim myArray As Variant, ws As Worksheet, pt As PivotTable
Dim i As Integer, ptcount As Integer
Dim ptname As String
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ptcount = ws.PivotTables.Count

For i = 1 To ptcount
ptname = ws.PivotTables(i).Name
ws.PivotTables(ptname).PivotCache.Refresh
Next i
Next
End Sub

The second macro will work with a single PT per sheet as well.
Again, change the array of sheet names to suit.
--

Regards
Roger Govier

"FredL" wrote in message
...
This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is
that I
have a Main Pivot table with calculations. I then have to group those
calculations together and so run a pivot of those. Since I can't do a
pivot
on pivot, I have a formula that points to the results. The second pivot
then
uses those formulas as its source.

With the solution below, those formulas doesn't have time to update when
the
main pivot refreshes. Therefore, the second pivot still has the results
from
the prior refresh. Any way around this?

"Roger Govier" wrote:

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a
template.
However, I have it specifying the Sheet number and Pivot Table names.
The
problem comes when a user would deletes or even hides a tab or copy
over a
pivot without renaming the table. Is there any way I can program it
such
that it will refresh all pivot tables in a tab that is between 2 tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot Table
names.

Thanks.

Hi

If ToolsoptionsCalculationis set to Automatic, then there should be no
need for any pause, as the calculations will have been performed by the time
the next refresh occurs.
Check your setting for Calculation mode.

--

Regards
Roger Govier

"FredL" wrote in message
...
What if I refresh the main pivot, do a pause so that the formulas can
catch
up, then refresh all; understandably, the main pivot will refresh again.
Is
there a pause command?

"Roger Govier" wrote:

Hi Fred

If there is only 1 Pivot table on each of your sheets, you could use the
following code

Sub RefreshPivots()
Dim myArray As Variant, ws As Worksheet
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ws.PivotTables(1).PivotCache.Refresh
Next
End Sub

Change the Array of Sheets to the names of your sheets, in the order you
want them calculated.

If there is more than one Pivot Table on each sheet, then use the
following
code

Sub RefreshPivots2()
Dim myArray As Variant, ws As Worksheet, pt As PivotTable
Dim i As Integer, ptcount As Integer
Dim ptname As String
Set myArray = Sheets(Array("Sheet2", "Sheet3", "Sheet5"))
For Each ws In myArray
ptcount = ws.PivotTables.Count

For i = 1 To ptcount
ptname = ws.PivotTables(i).Name
ws.PivotTables(ptname).PivotCache.Refresh
Next i
Next
End Sub

The second macro will work with a single PT per sheet as well.
Again, change the array of sheet names to suit.
--

Regards
Roger Govier

"FredL" wrote in message
...
This worked so well, that the formulas that the other pivot depends on
doesn't have a chance to get updated. So the way I have this setup is
that I
have a Main Pivot table with calculations. I then have to group those
calculations together and so run a pivot of those. Since I can't do a
pivot
on pivot, I have a formula that points to the results. The second
pivot
then
uses those formulas as its source.

With the solution below, those formulas doesn't have time to update
when
the
main pivot refreshes. Therefore, the second pivot still has the
results
from
the prior refresh. Any way around this?

"Roger Govier" wrote:

Hi Fred

Try using
ActiveWorkbook.RefreshAll


--

Regards
Roger Govier

"FredL" wrote in message
...
So I have this simple macro that refreshes the pivot tables in a
template.
However, I have it specifying the Sheet number and Pivot Table
names.
The
problem comes when a user would deletes or even hides a tab or copy
over a
pivot without renaming the table. Is there any way I can program it
such
that it will refresh all pivot tables in a tab that is between 2
tabs.

For example:
tab 1
tab 2
tab 3
tab 4

Refresh all pivots that are between tab 1 and 4.
Then I can add tabs in between and not have to worry about Pivot
Table
names.

Thanks.