Excel: Copy worksheets to new workbooks incl. Pivot Table


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


This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of May 24, 2017.


For more resources for Microsoft Excel