icekiwikiwi
New Member
- Joined
- Oct 20, 2021
- Messages
- 6
- Office Version
- 2016
- Platform
- Windows
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.
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.
VBA Code:
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