Copy and Paste From Pivot Tables Into New Workbook

[icekiwikiwi]

Hi everyone

I have a workbook with the same pivot table filtered around 25 different ways, each on a separate tab. I need to copy and paste it to a new document without the pivot tables, but still retaining the formatting.

I have butchered together the following code (which works) but I feel like there are probably a number of redundancies here. The code itself takes a few minutes to run so its quite resource intensive.

Any suggestions to streamline my code are appreciated! Thank you.

Sub Duplicate_Workbook

Dim Sh As Worksheet

Application.ScreenUpdating = False
Application.Cursor = xlWait


ActiveWorkbook.Sheets.Copy

     For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = True Then
            Sh.Activate
            Sh.Cells.Copy
            Sh.Range("A1").PasteSpecial Paste:=xlPasteValues
            Sh.Activate
            Sh.Cells.Copy
            Sh.Range("A1").PasteSpecial Paste:=xlPasteFormats
            Sh.Range("A1").Select
        End If
    Next Sh
    
Application.CutCopyMode = False

Application.ScreenUpdating = True
Application.Cursor = xlDefault

End Sub

 

[Alex Blakenburg]

I can give you code that will copy the actual pivot table formatting if you want it.
This code will reset the selected cell in the copied workbook.

Sub RepositionActiveCell()
   
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
        ws.Range("A1").Select
       
    Next ws  

End Sub

 

[Jon von der Heyden]

Now I just need to work out how to make sure each tab is unselected after being copied and pasted. It's currently left highlighted in the new document.

I like to use the GoTo method:

Call Application.Goto(Reference:=.Range("A1"),Scroll:=True)