Creating a pivot table via VBA

Resolved · Medium Priority · Version 2013

Tue 28 Jan 2014, 12:26 Delegate James said...

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

Tue 28 Jan 2014, 14:05 Edited on Tue 28 Jan 2014, 14:06 Trainer Gary said...

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