Help with Macro to COUNT for Pivot and Refresh Pivots

[Slomaro2000]

All,
I am in need of help with a macro to refresh a pivot tables. And also Count the Rows and Columns.

I have multiple tabs with a pivot on each. I did copy the main pivot so I was hoping to be able to refresh the main and it will refresh all of the rest?


The macro is pasting in the "DATA" tab then (trying) to count the Rows and Columns.

Then (trying) to refresh all of the "tabs" Pivots at once..


Here is what I have so. I did get it to work without counting the Columns so I will include that.

'***Verifying that "You" want to continue with the Macro***
Answer = MsgBox("Are you sure you want to update?", vbQuestion + vbYesNo)
If Answer = vbNo Then Exit Sub

'***Deleting Rows***
Sheets("Data").Select
Rows("6:60000").Select
Selection.ClearContents
Range("A6").Select

'***Paste in from Business Objects***
ActiveSheet.Paste

'***Deletes the Headers that came with the Business Objects Copy***
Rows("6:9").Select
Selection.Delete Shift:=xlUp
Range("D6").Select

'***This Refreshes the charts***
'***This Counts the number of row so the charts refresh with the correct number***
' Update Pivot Table with new rows
' Set Counters = 0
rowpvt = 4
colpvt = 0
' Use counter(colpvt) to determine columns for new pivot table
Sheets("Data").Select
Range("A5").Select
10 If ActiveCell.Value = "" Then GoTo 20
rowpvt = rowpvt + 1
ActiveCell.Offset(1, 0).Select
GoTo 10
20 Sheets("Spend Trend-mo").Select

'***This updates Pivot Tables with number of rows and columns***
Sheets("Spend Trend-mo").Select
Range("B35").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Data'!R5C1:R" & rowpvt & colpvt





'**These will work for me if im not counting the Columns and I refresh every single tab.


' Sheets("Spend Trend-mo").Select
' Range("B35").Select
' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Data'!R5C1:R" & rowpvt & "C27"

' Sheets("Spend Trend-qtr").Select
' Range("B35").Select
' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Data'!R5C1:R" & rowpvt & "C27"

Any help would be great.

 

[Slomaro2000]

Does anyone know if this is even possible?

If so I will keep trying.






Also if more info is needed just let me know..



Thanks, J

 

[gingerafro]

how about using a dynamic range for the 'Data' sheet and using this as the source for your pivottables.

You can then use this code to refresh all of your pivots:

Sub refreshpivots()

dim mypivot as pivottable

for each mypivot in activeworkbook.pivotcaches
on error resume next
mypivot.refresh
next pc

end sub

 

[Slomaro2000]

I will give it a shot..

J

 

[Slomaro2000]

Sorry,


Compile error:
Invalid Next control variable reference

**Its highlighting the (PC)**

Sub refreshpivots()
Dim mypivot As PivotTable
For Each mypivot In ActiveWorkbook.PivotCaches
On Error Resume Next
mypivot.Refresh
Next pc
End Sub

 

[Slomaro2000]

I think this is working.

haha I have been tinkering with it for quite a while now..

Sub RefreshPT()
Sheets("Spend Trend-mo").PivotTables("PivotTable1").RefreshTable
End Sub

Still need to know how to Count the Rows and Columns.. I have to be close with the original. I think at least..

 

[gingerafro]

sorry - the 'pc' should say 'mypivot'
If you name a dynamic range, you wouldn't need to tell the pivots what range of data to use.

 

[Slomaro2000]

Gingerafro,

Sorry to be a pain but its still giving me an error.


Run-time error '13':

Type Mismatch.




If this dynamic range will work and update the pivots with all data even when we add a column/rows this will be perfect..



Thanks for you time..


J

 

[Fazza]

Hi, J. You might be interested in below code? Regards, F

ThisWorkbook.RefreshAll

 

[chuf]

Fazza,
Does that code actually do just what it says on the tin ?
refreshes pivot tables etc. Genius !