how to use vba with pivot tables?

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
846
Office Version
  1. 2013
Platform
  1. Windows
I'm fairly new to VBA so dont know a lot of the basics.

I created a pivot table and recorded my doing so. This is just a simplified one that I could put up here, the one I actually need is a bit more complicated and would generate a lot more code.

Anyway so the problem I'm having is that apparently pivot tables have names (like PivotTable1, PivotTable2 etc). So with the code I generated it says PivotTable1, and when I create a new table it's called 2. When I run the code it looks for PT1 and then stops. What's my way around this?

Here's the code. Problematic parts bolded.

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Gender")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Score"), "Sum of Score", xlSum
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
instead of the name, you can use a number, an index, meaning the xth pivottable of that sheet.
Because you just added that pivottable, it 'll be the last pivottable, so count the pivottables with
iPVT=Activesheet.pivottables.count
replace now the bold text + double quotes with that number
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
With ActiveSheet.PivotTables(iPVT).PivotFields("Name")
 
Upvote 0
Thanks so much!

This still isn't working. Here's what's happening now.

The part that's underlined here is what's highlighted in VBA when I click Debug. The error is "unable to get the pivot tables property of the worksheet class"

Edit - also of note. I'm on Sheet 5 which has PivotTable2. But when I pressed F8 to execute line by line, iPVT's value was 2 not 1. Could it be that iPVT isn't the number of pivot tables in the entire workbook, it's the number of pivot tables on just the active sheet?
Edit 2 - I tried changing ActiveSheet to ActiveWorkbook to experiment. That just gave me an iPVT value of 0.


Sub Macro1()
iPVT = ActiveSheet.PivotTables.Count

With ActiveSheet.PivotTables("iPVT").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("iPVT").PivotFields("Gender")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("iPVT").AddDataField ActiveSheet.PivotTables( _
"iPVT").PivotFields("Score"), "Sum of Score", xlSum
End Sub
 
Upvote 0
it's iPVT without double quotes !!!
iPVT is a number and with those double quotes it became the string "iPVT"

With ActiveSheet.PivotTables(iPVT).PivotFields("Name")

check for example with this msgboxes all the pivottables in your sheet (didn't test the macro)
number 1 is the oldest (earliest created) pivottable, i suppose.

VBA Code:
with activesheet
for i=1 to .pivottables.count
msgbox i & "  " & .PivotTables(i).Name   'the name
msgbox i & "   " & .PivotTables(i).tablerange1.address 'the range
next
end with
 
Last edited:
Upvote 0
Thanks so much! This seems to be functioning fine now.

Couple questions. With the code you gave I wasn't completely sure what to do with it so I put it before the other code I have.

I also took the line you gave
.pivottables.count
and put it in a msgbox so it looks like this
MsgBox ".PivotTables.Count" & " = " & .PivotTables.Count


Now that MsgBox gives me 1 as a result even when I have multiple pivot tables in the workbook. Does that mean it's giving me the number of pivots on the sheet as opposed to in the workbook? I've never actually seen a command like this before, where you start with a decimal.

For this command
MsgBox i & " " & .PivotTables(i).TableRange1.Address 'the range

I keep getting A3:C20, which is strange because the range is actually A1:D6. I've sometimes chosen a different range, like A1:C6 and it still gives the same result. I just chose A1:D2 and it still gave me the same result. (edit - sorry I think it might be giving A3:C20, not A1).

Any idea what's happening there?
 
Upvote 0

Forum statistics

Threads
1,225,156
Messages
6,183,246
Members
453,152
Latest member
ChrisMd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top