allison
03-24-2008, 10:56 AM
I'm trying to write code for a pivot table and keep erroring out. I need some help in fixing the error, please.
This is the code:
Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
' Create a PivotCashe
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
' Add New Worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
' Create the Pivot Table from the Cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="NOCPivot")
With PT
' Add fields
.PivotFields("a").Orientation = xlRowField
.PivotFields("b").Orientation = xlRowField
.PivotFields("d").Orientation = xlDataField
End With
Application.ScreenUpdating = True
End Sub
I get a run-time error '1004' with "The PivotTable name is not valid".
my data looks like this (the a,b,c are in row 1 as sample column names)
abc d e fABLMNEW 000ABLMCLOSED 100ABLMOPEN/PENDING000AKLMNEW 000AKLMCLOSED 000AKLMOPEN/PENDING000AKMBNEW 000AKMBCLOSED 000
If I keystroke it, it works, but I'm trying to make it more generic because I need to repeat the process for 8 different datasets, all of varying row lengths.
This is the code:
Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
' Create a PivotCashe
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
' Add New Worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
' Create the Pivot Table from the Cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="NOCPivot")
With PT
' Add fields
.PivotFields("a").Orientation = xlRowField
.PivotFields("b").Orientation = xlRowField
.PivotFields("d").Orientation = xlDataField
End With
Application.ScreenUpdating = True
End Sub
I get a run-time error '1004' with "The PivotTable name is not valid".
my data looks like this (the a,b,c are in row 1 as sample column names)
abc d e fABLMNEW 000ABLMCLOSED 100ABLMOPEN/PENDING000AKLMNEW 000AKLMCLOSED 000AKLMOPEN/PENDING000AKMBNEW 000AKMBCLOSED 000
If I keystroke it, it works, but I'm trying to make it more generic because I need to repeat the process for 8 different datasets, all of varying row lengths.