Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default Need to update Multiple PivotTables from same sorce

I inherit a XLS file that has multiple pivottables, and no underlying data. I
have to refresh each of these tables one at a time. (yuck) is there an option
I am missing? What VBA could I use to make this easier for me?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default Need to update Multiple PivotTables from same sorce

I took a stab at it, but I'm sure there is a more efficient way of doing this:

Sub Refresh_PivotTables()

' Select L2 Pivots Sheet and update
Sheets("L2 Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh

' Select Deskside Pivots shee and update
Sheets("Deskside Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable7").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable8").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable9").PivotCache. Refresh

End Sub

"Mike" wrote:

I inherit a XLS file that has multiple pivottables, and no underlying data. I
have to refresh each of these tables one at a time. (yuck) is there an option
I am missing? What VBA could I use to make this easier for me?

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Need to update Multiple PivotTables from same sorce

Maybe...

Option Explicit
Sub DoThemAll()
ThisWorkbook.RefreshAll
End Sub



Mike wrote:

I took a stab at it, but I'm sure there is a more efficient way of doing this:

Sub Refresh_PivotTables()

' Select L2 Pivots Sheet and update
Sheets("L2 Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh

' Select Deskside Pivots shee and update
Sheets("Deskside Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable7").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable8").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable9").PivotCache. Refresh

End Sub

"Mike" wrote:

I inherit a XLS file that has multiple pivottables, and no underlying data. I
have to refresh each of these tables one at a time. (yuck) is there an option
I am missing? What VBA could I use to make this easier for me?


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,101
Default Need to update Multiple PivotTables from same sorce

Thanks... its my first day.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub DoThemAll()
ThisWorkbook.RefreshAll
End Sub



Mike wrote:

I took a stab at it, but I'm sure there is a more efficient way of doing this:

Sub Refresh_PivotTables()

' Select L2 Pivots Sheet and update
Sheets("L2 Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh

' Select Deskside Pivots shee and update
Sheets("Deskside Pivots").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable6").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable7").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable8").PivotCache. Refresh
ActiveSheet.PivotTables("PivotTable9").PivotCache. Refresh

End Sub

"Mike" wrote:

I inherit a XLS file that has multiple pivottables, and no underlying data. I
have to refresh each of these tables one at a time. (yuck) is there an option
I am missing? What VBA could I use to make this easier for me?


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple PivotTables Excellerate Excel Discussion (Misc queries) 0 January 22nd 08 11:58 PM
Multiple PivotTables on one spreadsheet AndyL82 Excel Discussion (Misc queries) 1 April 30th 07 06:52 AM
[pivottables] multiple table selection fields update in one click... [email protected] Excel Discussion (Misc queries) 1 September 22nd 06 02:42 AM
How do I update a ODBC query in Excel using pivotTables in VBA? FCS Excel Programming 6 November 10th 05 04:47 PM
Update pivottables Henrik Excel Discussion (Misc queries) 3 October 10th 05 03:58 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"