Solved: Pivot Table Help

[allison]

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.

[Bob Phillips]

Should it just be



With PT
' Add fields
.PivotFields("a").Orientation = xlRowField
.PivotFields("b").Orientation = xlRowField
.PivotFields("c").Orientation = xlDataField
End With

[allison]

So, I've got too much in there, eh? :-) Is that something I'll learn just from use? I tried to structure it the way I read about in a Waldenbach book since it was my first time trying to code a table.

I took out the extraneous stuff and nwo I get another run-time error '91' - Object variable or With block variable not set. It shows up on the line

..PivotFields("a").Orientation = xlRowField

[Bob Phillips]

No I didn't mean that, I was just giving that portion of code that I changed to make it work for me.

[allison]

I apologize, in advance, for just not getting it.

I made the same change and I'm still getting the same 1004 error. Could it be something with the sheet name on the workbook? I'm uploading the workbook.

[Bob Phillips]

Sub CreatePivotTable()

Dim PTCache As PivotCache
Dim Pt As PivotTable
Dim this As Worksheet

Application.ScreenUpdating = False

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("PivotSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Create a PivotCashe
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)

' Create the Pivot Table from the Cache
Set Pt = PTCache.CreatePivotTable( _
TableDestination:="", _
TableName:="PivotTable2")

ActiveSheet.Name = "PivotSheet"

With Pt
' Add fields
.PivotFields("a").Orientation = xlRowField
.PivotFields("b").Orientation = xlRowField
.PivotFields("c").Orientation = xlDataField

End With

Application.ScreenUpdating = True
End Sub

[allison]

XLD,

As always, thanks so much. I'm still not able to make it work at work (2003), but I'm messing with it at home (2007) and it works wonderfully. I appreciate your willingness to help!

[Bob Phillips]

That's odd, I did it in 2003 not 2007.

[allison]

I've got it working at work now. The difference - I recreated the test data. I'm getting the data sent to me and while I thought that I imported and changed the formatting correctly, I'll bet dollars to donuts that I didn't.