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
    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
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