Patrick,
When I STEP (F8) the code the "ActiveWorkbook.RefreshAll" refreshes ALL
sheets, including the Pivots with the old data (as expected). The
"PivotCache.Refresh" loop refreshes the Pivots with the new data (as
expected).
The "Application.ScreenUpdating" didn't seem to matter TRUE or FALSE.
But if I run it from the CmdButton or F5, I only get the 1st "RefreshAll".
It only updates the data sheets w/new & the Pivots w/old data. It's like it
needs some kind of delay before it executes the "PivotCache.Refresh". I
assumed the 1st MsgBox cmd would take care of that. That's why I also tried
a delay by calling a 2nd sub. In both cases, it's going all the way through
the code. Because I get both MsgBoxes.
Also, how would I include a following into my Dim/For/Next?
dt.Range("Z1") = "Refresh date: " & Format(Now(), "mm/dd/yyyy")
--
Thanks, Kevin
"Patrick Molloy" wrote:
> your loop looks good to me. Switch screen updating back to TRUE
> What happens if you STEP (F8) through?
> nothing looks wrong to me.
>
> "AFSSkier" wrote:
>
> > I'm trying 1st refresh the data sheets connected to an outside database.
> > Then refresh all of the end user's custom pivot tables.
> >
> > I've tried a few different types of code. But I only get the data sheets to
> > refresh & not the pivot tables. If I seperate the code into 2 different
> > CmdButtons, I can get them to work. But I only what one CmdButton.
> >
> > Private Sub CmdRefreshAll_Click()
> > 'Refresh data sheets for outside data sources
> > ActiveWorkbook.RefreshAll
> > 'or something else here to refresh only inbound data sheets.
> >
> > MsgBox "All Data Sheets are updated, click ok to update Pivot tables"
> >
> > 'Refresh Pivot tables
> > Dim Wks As Worksheet
> > Dim pvtTable As PivotTable
> >
> > Application.ScreenUpdating = False
> >
> > For Each Wks In ActiveWorkbook.Worksheets
> > For Each pvtTable In Wks.PivotTables
> > pvtTable.PivotCache.Refresh
> > Next pvtTable
> > Next Wks
> >
> > MsgBox "All Data Sheets & Pivot Tables are updated"
> >
> > End Sub
> >
> > I also tried calling a 2nd macro:
> >
> > Private Sub CmdRefreshAll_Click()
> > 'Refresh data sheets for outside data sources
> > ActiveWorkbook.RefreshAll
> > MsgBox "All Data Sheets are updated, click ok to update Pivot tables"
> > Call RefreshPivots
> > End Sub
> >
> > Private RefreshPivots()
> > 'Refresh Pivot tables
> > Dim Wks As Worksheet
> > Dim pvtTable As PivotTable
> >
> > Application.ScreenUpdating = False
> >
> > For Each Wks In ActiveWorkbook.Worksheets
> > For Each pvtTable In Wks.PivotTables
> > pvtTable.PivotCache.Refresh
> > Next pvtTable
> > Next Wks
> > MsgBox "All Data Sheets & Pivot Tables are updated"
> > End Sub
> >
> > I'd also like to add the date the sheets were refreshed, in the same code.
> > I assume it would go something like this.
> >
> > dt.Range("Z1") = "Refresh date: " & Format(Now(), "mm/dd/yyyy")
> >
> > --
> > Thanks, Kevin
|
Similar Threads
