Copy worksheets to new workbooks incl. Pivot Table

[OBMIKEE]

I have a workbook that contains multiple worksheets
I wish to copy each ws to a new wb and save each seperately.

This works well with below, until i introduce a pivot table into the worksheets.
1004 - you cannot nove part of a PivotTable or insert worksheet cells rows..........

Sub Generate_Files()
'
' Generate_files
'


'
  Dim sheetnames As Variant
  
  sheetnames = Array("STRATEGY", "MARKETING", "GROUP", "INNOVATION")


  Dim i As Long
  For i = 0 To 36
    Windows("Dev_workbook.xlsm").Activate
    Sheets(sheetnames(i)).Select
    Sheets(sheetnames(i)).Copy
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    Path = "C:\Users\xyz\Desktop\Mike"
    Filename = sheetnames(i) & Range("G1") & Range("F1")
   ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   ActiveWorkbook.Close
    
    Next
End Sub

Any assistance appreciated

 

[RoryA]

Not too surprising then - you removed the line that copies the worksheet!

Also, please use code tags when posting code.

 

[OBMIKEE]

Same behaviour changing those two lines to
Sub Generate_Files()
'
' Generate_files
'


'
Dim sheetnames As Variant

sheetnames = Array("STRATEGY", "MARKETING", "GROUP", "INNOVATION")


Dim i As Long
For i = 0 To 36
Windows("Dev_workbook.xlsm").Activate
Sheets(sheetnames(i)).Select
ActiveSheet.UsedRange.Copy
ActiveSheet.UsedRange.PasteSpecial xlPasteValues
Path = "C:\Users\obriemik\Desktop\Mike\Mike1"
Filename = sheetnames(i) & Range("G1") & Range("F1")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close

Next
End Sub


Tried both to see if it gets back into loop

 

[OBMIKEE]

Thanks @RoryA

Sometimes you can look at the same code for far too long and not see the wood for the trees