Pivot Table: Copy and Paste but Rename It?

[larinda4]

Good morning,

I have a pivot table in cells A:C, and I want to copy A:C and paste in F:H, but rename the pivot table. My only problem is when I close the book and re-open it to try running the macro again, the pivot table name is different
ex "Pivottable3" is now "pivottable6" and I get a debug error.

Is there any way to copy and paste the pivot table, but immediately rename it without having to reference "pivottable##" to do it?

Here is my code:

 'Copy current PT and paste to column F.
    Columns("A:C").Select
    Selection.Copy
    Columns("F:F").Select
    ActiveSheet.Paste
    
'Update Region Filter
    ActiveSheet.PivotTables("EAPPT7").PivotFields("Region").ClearAllFilters
    ActiveSheet.PivotTables("EAPPT7").PivotFields("Region").CurrentPage = "0007"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = _
        "0008"

Here is the code to create the PT but I know this only works if there's one pivot table in the sheet. So should I create another insert PT macro instead of trying to rename the copied pivot table?

'Insert EAP pivot table

Dim dataSheet2 As Worksheet
Set dataSheet2 = Sheets("Listing")

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
               SourceData:="'" & dataSheet2.Name & "'!" & dataSheet2.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable _
                  TableDestination:=Sheets("EAPPT").Range("A3")
    Sheets("EAP").Select
    Cells(1, 1).Select
        'Rename Pivot Table
    With Sheets("EAPPT")
        .PivotTables(1).Name = "EAPPT7"
    End With

 

[larinda4]

Hey everyone,

I found a solution.

Instead of copying and pasting the pivot table, I created another code for inserting a pivot table beside the one I already created.

Thanks!

 

[larinda4]

In case anyone wants the code I used to create the pivot table, here it is below:

    With Worksheets("Listing")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Listing!R1C1:R" & lastRow & "C21", Version:=6).CreatePivotTable _
        TableDestination:="EAPPT!R3C6", TableName:="EAPPT8", DefaultVersion _
        :=6
    Sheets("EAPPT").Select
    Cells(1, 6).Select
    With ActiveSheet.PivotTables("EAPPT8")