PDA

View Full Version : Solved: PivotTable Name issue



kathyb0527
07-13-2010, 03:12 PM
I copied the code provided by XLD from the following thread and updated as needed.http://vbaexpress.com/forum/showthread.php?t=32379&highlight=pivot+table
I keep getting error 1004 the PivotTable filed name is not valid at the section with the table name. Which is the same problem that XLD fixed in this code. Can someone help please?
Sub Pivot()

' pivot Macro

Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Bench Top")
Dim PTOutput As Worksheet

Dim PTCache As PivotCache
Dim PRange As Range

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pivot Table"
Set PTOutput = Worksheets("Pivot Table")


' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(2, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(2, 1).Resize(finalRow - 1, finalCol)


Set pt = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="StabilityPivot")

' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True

' Set up the row fields
pt.PivotFields("Watson Id " & Chr(10) & " ").Orientation = xlRowField
' Set up the data fields
With pt.PivotFields("Frozen " & Chr(10) & "Condition")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With

' Now calc the pivot table
pt.ManualUpdate = False

End Sub

THANKS!

kathyb0527
07-14-2010, 03:10 PM
One of my columns did not have a title. Sometimes the answer is right in front of you!

Aussiebear
07-14-2010, 11:17 PM
All too common feeling with most of us I'm afraid