I copied the code provided by XLD from the following thread and updated as needed.http://vbaexpress.com/forum/showthre...ht=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?
[vba]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
[/vba]
THANKS!
Reply With Quote
