A
Aaron
I have the below code and getting a error (Pivot Table name not valid) when
the code is ran
Sub CreatePivotTables()
Dim LastRow As Long
Worksheets.Add.Name = "Receiving TAT"
Worksheets.Add.Name = "Screening TAT"
Sheets("HP Defective Receipts in SPL ma").Select
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
TableDestination:=Range("A1"), TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving
TAT"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Receiving TAT")
.Orientation = xlDataField
.Calculation = xlPercentOfColumn
End With
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
the code is ran
Sub CreatePivotTables()
Dim LastRow As Long
Worksheets.Add.Name = "Receiving TAT"
Worksheets.Add.Name = "Screening TAT"
Sheets("HP Defective Receipts in SPL ma").Select
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
TableDestination:=Range("A1"), TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Receiving
TAT"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Receiving TAT")
.Orientation = xlDataField
.Calculation = xlPercentOfColumn
End With
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub