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.

 

[gingerafro]

sorry, this one works...

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

all this macros does is update all of your pivottables. You need to set a dynamic range separately.

try this site: http://www.contextures.com/xlNames01.html#Dynamic

 

[Slomaro2000]

Thanks that updates them.

I did the dynamic (first time I have used that) But when I ran the macro to Refresh it didn't updated all of the Rows/Columns. If I go into the Wizard it is saying I have way more than I really do.




J

 

[gingerafro]

must be something wrong with the dynamic range. are you sure the cells below the last 'populated' cells are really blank? no odd spaces in a cell or something?

 

[Slomaro2000]

Yeah.


What I have is a spread sheet with a bunch of tabs.

I run a Business Objects query then copy all.

It then goes into the spread sheet selects the 'Data' tabe and selects all rows then DELETEs the info.

Then it pastes in the new info.


That is when I want it to COUNT Rows/Columns and fix all of the pivots. I can get it to do the first one but I can't get it to do all of them.

I can get them to go if I "Select" the sheet and then update the Wizard. But I would like it to do "all" that way if there is something added to a column I will not have to change the macro.


J

 

[Slomaro2000]

This is what I have for the Row/Column count..

Sub UpdatePivotTablewithnewrows()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select

' Update Pivot Table with new rows
' Set Counters = 0
rowpvt = 4
colpvt = 0

' Calculate number of rows to copy and paste into Raw Data tab
Sheets("Data").Select
Range("a5").Select
10 If ActiveCell.Value = "" Then GoTo 20
rowpvt = rowpvt + 1
ActiveCell.Offset(1, 0).Select
GoTo 10


' Use counter(colpvt) to determine columns for new pivot table
20 Sheets("Data").Select
Range("A5").Select
50 If ActiveCell.Value = "" Then GoTo 60
colpvt = colpvt + 1
ActiveCell.Offset(0, 1).Select
GoTo 50
' Use counter(rowpvt) to determine rows for new pivot table
60 Sheets("Spend Trend-mo").Select
Range("B32").Select
40 ActiveCell.Offset(1, 0).Select
GoTo 30
30 If ActiveCell.Value = "" Then GoTo 40
' Update Pivot Table with number of rows and columns


ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="'Data'!R5C1:R" & rowpvt & "C" & colpvt & ""
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh



Next sh


'**********
'********** The macro works on the first tab but not all of the rest**************




ThisWorkbook.RefreshAll



' Sheets("Spend Trend-qtr").PivotTables("PivotTable1").RefreshTable


End Sub

 

[EugeneCarter]

Would =COUNTA(5:5) and =COUNTA(A:A) suffice?

 

[Slomaro2000]

Would =COUNTA(5:5) and =COUNTA(A:A) suffice?

??

Sorry, Im pretty new at this.

Are you saying paste that in the "reffers to:" in the Dynamic Name?

 

[EugeneCarter]

No, If you put those into your excel sheet. I'm wondering if that's the intent of your 'count rows', is that the value you are trying to obtain.

it looks as if you have a pivot table on a sheet, and you are just counting the headings until there's a blank. So I'm thinking CountA may give that value as well.

Once you have the row/col count, what's being done with it?

 

[Slomaro2000]

The pivot wizard needs to be updated. There are multiple sheets with pivots on each that needs to be updated.

 

[EugeneCarter]

I'm not sure if you know this, but if you have multiple pivot tables using the same source, you can have them using the same data, and when you refresh one, it refreshes them all automatically.

Also, I'm not sure why you clear rows 6+, but then start counting in row 5. Maybe it's because I am not seeing the file in front of me.
Not sure why you add +4 instead of +5.. but those are things you can tweak.

Here's a version of what you wrote using "CurrentRegion.Rows.Count", see if that is nicer for you.

'***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").Rows("6:60000").ClearContents
'***Paste in from Business Objects***
' Sheets("Data").Select
' Range("A6").Select
' ActiveSheet.Paste
'***Deletes the Headers that came with the Business Objects Copy***
Rows("6:9").Delete Shift:=xlUp
'***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 = Range("A5").CurrentRegion.Rows.Count + 4
colpvt = Range("A5").CurrentRegion.Columns.Count + 0
'***This updates Pivot Tables with number of rows and columns***
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R6C1:R" & rowpvt & "C" & colpvt).CreatePivotTable TableDestination:= _
"'Spend Trend-mo'!R35C2", TableName:="myPivot1", DefaultVersion:= _
xlPivotTableVersion10

Then name each pivot table myPivot2, etc. so you can refresh them individually if you wish.