Dynamic VBA Macro to Create Pivot Table

Bkisley

Board Regular
Joined
Jan 5, 2017
Messages
73
I have tried everything I know so far with no success.

Trying to do
I have a macro that creates a new tab and converts a data set into a formatted table named "Data Table"
The next macro (until I get this one working which I will then combine with my first macro) needs to create a new tab called "Pivot Table" and create a Pivot Table named "Pivot Table Final" of the formatted table named "Data Table" in cell A1. You will see the range I have is A1:R1000 this is just to be sure everything is captured.

This is where I keep getting stuck. It shows "invalid procedure call or argument". I have tried changed the table name directly in the code below and removing the last part of the code shown here, but still nothing.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Date Table!R1C1:R1000C18", Version:=6).CreatePivotTable TableDestination:= _
"Pivot Table!R1C1", TableName:="PivotTable3", DefaultVersion:=6
ActiveSheet.PivotTables("PivotTable3").Name = "PivotTableFinal"

PLEASE HELP!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
142
Office Version
  1. 2010
Platform
  1. Windows
Try this (didn't check):
VBA Code:
Dim PTCache As PivotCache, PTable As PivotTable, psh As Worksheet

Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("YourDataSheetName").Range("A1").CurrentRegion)
  
ActiveWorkbook.Sheets.Add Before:=Worksheets(1)
Set psh = Worksheets(1):    psh.Name = "PivotTable"
  
Set PTable = ActiveSheet.PivotTables.Add(PTCache, Range("A1"))
PTable.Name = "PivotTableFinal"
 

Watch MrExcel Video

Forum statistics

Threads
1,114,440
Messages
5,547,928
Members
410,819
Latest member
mendee525
Top