Pivot table help

[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

 

[GTVT06]

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("ReceivingTAT")
        .Orientation = xlDataField
        .Calculation = xlPercentOfColumn
    End With
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Receiving TAT").Range("A1")
    ActiveChart.Location Where:=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

 

[Aaron]

Name changes each time. I think I also may have messed up on Table
Destination. I need it to goto tab Receiving TAT.

 

[GTVT06]

Name changes each time.  I think I also may have messed up on Table
Destination. I need it to goto tab Receiving TAT.






- 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.

 

[Aaron]

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:=xlDatabase, SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
TableDestination:=Sheets("Screening TAT").Range("A1"),
TableName:="RTAT"

 

[GTVT06]

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:=xlDatabase, SourceData:= _
        "'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
        TableDestination:=Sheets("Screening TAT").Range("A1"),
TableName:="RTAT"






- 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:=xlDatabase,
SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" &
LastRow).CreatePivotTable _
TableDestination:=Sheets("Screening TAT").Range("A1") _
, TableName:="RTAT"

 

[Aaron]

Hi GTVT06
I changed the following code and it works, but it gets hung up on this code
now...

Code Change - added Column ("C22")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow &
"C22").CreatePivotTable _
TableDestination:=Sheets("Receiving TAT").Range("A1"),
TableName:="RTAT"

New Error on code: (Unable to get the Pivottable property of the Worksheet
class)
ActiveSheet.PivotTables("RTAT").SmallGrid = False

 

[GTVT06]

Hi GTVT06
I changed the following code and it works, but it gets hung up on this code
now...

Code Change - added Column ("C22")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'HP Defective Receipts in SPL ma'!R1C22:R" & LastRow &
"C22").CreatePivotTable _
        TableDestination:=Sheets("Receiving TAT").Range("A1"),
TableName:="RTAT"

New Error on code: (Unable to get the Pivottable property of the Worksheet
class)
ActiveSheet.PivotTables("RTAT").SmallGrid = False







- Show quoted text -

Oh, I believe that's because if the pivot table wasn't pre-existing
yet, it wont have any contents to clear. try this modified version and
let me know if it works.

On Error Resume Next
Sheets("Sheet3").PivotTables("RTAT").PivotSelect "",
xlDataAndLabel, True
Selection.ClearContents
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"'Sheet1'!A1:G22").CreatePivotTable _
TableDestination:=Sheets("Sheet3").Range("A1") _
, TableName:="RTAT"
ActiveSheet.PivotTables("RTAT").SmallGrid = False
On Error GoTo 0

 

[GTVT06]

Oh, I believe that's because if the pivot table wasn't pre-existing
yet, it wont have any contents to clear. try this modified version and
let me know if it works.

    On Error Resume Next
    Sheets("Sheet3").PivotTables("RTAT").PivotSelect "",
xlDataAndLabel, True
    Selection.ClearContents
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
        "'Sheet1'!A1:G22").CreatePivotTable _
        TableDestination:=Sheets("Sheet3").Range("A1") _
        , TableName:="RTAT"
        ActiveSheet.PivotTables("RTAT").SmallGrid = False
    On Error GoTo 0- Hide quoted text -

- Show quoted text -

Sorry I forgot to change all the names on my code to match yours.
Change Sheet3 to the appropriate sheet name and also change the source
data ("'Sheet1'!A1:G22")) to fit your needs.