This is the code I have...This same code works in a different sheet (I copied and pasted it and changed the necessary names) but I get an error in this new workbook...anyone know why?
Sub pt()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Metro-E Tasks")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Metro-E Fiber Ready_Core Ready")
Dim PTCache As PivotCache
Dim PRange As Range
Sheets("Metro-E Fiber Ready_Core Ready").Select
' Find the last row with data
Dim lastRow As Long
lastRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(lastRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange) <----- THIS IS WHERE THE ERROR OCCURS
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(3, 1), _
TableName:="PTD Change Rootcause")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
pt.AddFields RowFields:=Array( _
"PTD Change Root cause"), ColumnFields:=Array("Customer Name")
' Set up the data fields
With pt.PivotFields("Customer Name")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
ActiveSheet.PivotTables("PTD Change Root cause").TableStyle2 = "PivotStyleLight16"
End Sub
Sub pt()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Metro-E Tasks")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Metro-E Fiber Ready_Core Ready")
Dim PTCache As PivotCache
Dim PRange As Range
Sheets("Metro-E Fiber Ready_Core Ready").Select
' Find the last row with data
Dim lastRow As Long
lastRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(lastRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange) <----- THIS IS WHERE THE ERROR OCCURS
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(3, 1), _
TableName:="PTD Change Rootcause")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
pt.AddFields RowFields:=Array( _
"PTD Change Root cause"), ColumnFields:=Array("Customer Name")
' Set up the data fields
With pt.PivotFields("Customer Name")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
ActiveSheet.PivotTables("PTD Change Root cause").TableStyle2 = "PivotStyleLight16"
End Sub