Pivot Table Creation Error

[Loonybin]

I am a beginner at VBA and coding all together. I have been working on coding a macro for creating a pivot table and I don't understand what is wrong with my code.

The Bolded and Underlined line is the where the error is coming up

Excel 2013

ERROR CODE:

Run-tie error '424': Object Required



Sub CreatePivotTable()



Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String



'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:BU99999").Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
Set sht = Sheets.Add
sht.Name = ("Pivot Table")


'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)

'Select "Data Dump"
ThisWorkbook.Sheets("Data Dump").Select


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorksheet.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)



'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="IAmPivot")

pvt.PivotFields("Module").Orientation = xlRowField
pvt.PivotFields("KW").Orientation = xlColumnField
pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12
End Sub

[mana]

Set pvtCache = ActiveWorkBook.PivotCaches.Create(

[Loonybin]

Set pvtCache = ActiveWorkBook.PivotCaches.Create(

Thanks! that helped with that line. But now I'm having troubles with the next line.

ERROR CODE:

Run-time error '5': Individual procedure call or argument


Again Error code is marked with bolded and underlined


Sub CreatePivotTable()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:BU99999").Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
Set sht = Sheets.Add
sht.Name = ("Pivot Table")

'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)
'Select "Data Dump"
ThisWorkbook.Sheets("Data Dump").Select

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="IAmPivot")


pvt.PivotFields("Module").Orientation = xlRowField
pvt.PivotFields("KW").Orientation = xlColumnField
pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12

End Sub

[mana]

StartPvt = "'" & sht.Name & "'!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)


or


StartPvt = sht.Range("A1").Address(ReferenceStyle:=xlR1C1, external:=True)

[Loonybin]

StartPvt = "'" & sht.Name & "'!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)


or


StartPvt = sht.Range("A1").Address(ReferenceStyle:=xlR1C1, external:=True)


Well now it runs the macro with no error in both cases, but there is still no pivot table on the newly made spreadsheet

[mana]

Could you please upload your workbook?

[Paul_Hossler]

I think some of your references were wrong -- I made them ranges

I just made some dummy data for my test




Option Explicit
Sub CreatePivotTable()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As Range
Dim SrcData As Range



'Determine the data range you want to pivot
Set SrcData = ActiveSheet.Cells(1, 1).CurrentRegion


'Create a new worksheet
Set sht = Sheets.Add
sht.Name = "Pivot Table"


'Where do you want Pivot Table to start?
Set StartPvt = sht.Cells(1, 1)

'Select "Data Dump"
' ThisWorkbook.Sheets("Data Dump").Select


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)


'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="IAmPivot")

pvt.PivotFields("Module").Orientation = xlRowField
pvt.PivotFields("KW").Orientation = xlColumnField
pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12
End Sub