On Jan 21, 2:21*pm, Aaron wrote:
Ok, I tried putting a unique name everywhere that pivottable1 is and I get
the same error. *The debugger stops on this code.

* * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
* * * * "'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
* * * * TableDestination:=Sheets("Screening TAT").Range("A1"),
TableName:="RTAT"



"GTVT06" wrote:
On Jan 21, 2:00 pm, Aaron wrote:
Name changes each time. *I think I also may have messed up on Table
Destination. I need it to goto tab Receiving TAT.

"GTVT06" wrote:
On Jan 21, 1:46 pm, Aaron wrote:
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:=xlDatab ase, 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 Whe=xlLocationAsNewSheet

End Sub

Is the pivot table name "PivotTable1" every time you run this code? or
is the name changing each time? i.e. PivotTable1, PivotTable2,
PivotTable3- Hide quoted text -

- Show quoted text -

ok, the (Pivot Table name not valid) error would be because it's
looking for "PivotTable1" each time, and since the name is changing
each time PivotTable1 no longer exist so it's an invalid name. you may
want to give the Pivot Table a unique name in the macro so each time
it's ran, the name stays the same.- Hide quoted text -

- Show quoted text -

Try to clear contents on the table before reusing it... Try replacing
the code you just posted with this code.

Sheets("Screening TAT").PivotTables("RTAT").PivotSelect "",
xlDataAndLabel, True
Selection.ClearContents
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
TableDestination:=Sheets("Screening TAT").Range("A1") _
, TableName:="RTAT"