James has attended:
Excel Advanced - Formulas & Functions course
Excel VBA Intro Intermediate course
Creating a pivot table via VBA
Hi,
I am trying to create a pivot table at the end of my VBA code in a new sub routine. The problem comes from the fact the pivot table is in a newly created workbook.
When I use this line of code:
Set objTable = Sheet2.PivotTableWizard
It creates a pivot table in the new workbook but the available fields are those within the workbook that the macro is in not the workbook/ worksheet the macro has created.
I think I have to use Pivot cache but I am not sure.
I hope this makes sense! Any help would be appreciated.
Thanks
James
RE: Creating a pivot table via VBA
Hi James
Good to hear from you. Yes, the PivotCache is the main object you want. The best way to work with a PivotTable is to create a variable for the source range, and another variable for the PivotTable itself.
I've attached an example which creates a new PivotTable in a new workbook. Press ALT+F11 when you've opened the file.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
