Macro to Create Pivot Table on Existing WorkSheet

[Legacy 367316]

Trying to created a Pivot Table on an existing worksheet. This is the code that was recorded during the record macro. I stopped recording and then ran the code but when I go to run it again it gives me an error. Run-time error '5': invalid procedure call or argument
Any help would be greatly appreciated.


Sub Macro12()
'
' Macro12 Macro
'

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Outage Data!R1C1:R526C9", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="CEMI Devices!R3C5", TableName:="PivotTable4", _
DefaultVersion:=xlPivotTableVersion14




End Sub

 

[Misca]

I believe this tutorial is all you need to know about creating pivot tables with VBA: How To Create Pivot Table Using VBA | Excel & VBA – Databison

And if that's not enough: The VBA Guide To Excel Pivot Tables

 

[dave3009]

Hazard a guess at "PivotTable4" already existing, if you want to recreate the pivot table you'll need to delete it first.

 

[Sgdva]

Trying to created a Pivot Table on an existing worksheet. This is the code that was recorded during the record macro. I stopped recording and then ran the code but when I go to run it again it gives me an error. Run-time error '5': invalid procedure call or argument
Any help would be greatly appreciated.


Sub Macro12()
'
' Macro12 Macro
'

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Outage Data!R1C1:R526C9", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="CEMI Devices!R3C5", TableName:="PivotTable4", _
DefaultVersion:=xlPivotTableVersion14




End Sub

The problem is within the SourceData, excel may not give the proper code even when it's being recorded.
What I did was declare the pivot table as variable:

Dim Pt As PivotTable
Sheets("desired_Sheet").Select
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("desired_Sheet").Range("A1").CurrentRegion, _
    Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="pivot_table!R1C1", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14)

-probably selecting the sheet isn't necessary-

You may do many thing with the Pt being declared as variable with the command "With PT -or however you named your pivottable variable- "

 

[Legacy 367316]

I changed the code a bit with your guys suggestions but still no luck. I corrected the source data and the pivot table name. I believe the issue is with the table destination, I want the pivot table in sheet CEMI Devices and in cell E3

Sub Macro12()
'
' Macro12 Macro
'

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Event Table!R1C1:R82C15", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="CEMI Devices!R3C5", TableName:="PivotTable99", _
DefaultVersion:=xlPivotTableVersion14




End Sub

 

[Legacy 367316]

Also tried with no luck

Sub Macro12()
'
' Macro12 Macro
'

Dim Pt As PivotTable
Sheets("CEMI Devices").Select
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Event Table").Range("A1").CurrentRegion, _
Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="CEMI Devices!R1C1", TableName:="PivotTable1001", _
DefaultVersion:=xlPivotTableVersion14)




End Sub

 

[Sgdva]

Also tried with no luck

Sub Macro12()
'
' Macro12 Macro
'

Dim Pt As PivotTable
Sheets("CEMI Devices").Select
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("Event Table").Range("A1").CurrentRegion, _
Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="CEMI Devices!R1C1", TableName:="PivotTable1001", _
DefaultVersion:=xlPivotTableVersion14)




End Sub

I remember why I don't use spaces in sheet names, that seems to be the problem, try to change "Event Table" to Event_Table and "CEMI Devices" to "CEMI_Devices"
And yeah, you don't have to select the sheet.

Sub CreatePivotTable()
[COLOR=#333333]Dim Pt As PivotTable[/COLOR]
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Sheets("Event_Table").Range("A1:Z100"), _
    Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="CEMI_Devices!R1C1", TableName:="PivotTable2", _
    DefaultVersion:=xlPivotTableVersion14)
End Sub

Change the Range as you wish for both pivot table and in the sheets Event_Table