Most valuable professional
  • Hot Topics

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.......... Code: Sub Generate_Files() ' ' Generate_files ' ' 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

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:

This article includes the following tags:

  • Excel
  • Microsoft Excel