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..........
Any assistance appreciated
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
This question generated 13 answers. To proceed to the answers, click here.
This thread is current as of May 24, 2017.