I have one large Excel workbook with multiple worksheets containing pivot tables linked to a big PowerPivot source. I want to save each worksheet separately into workbooks, only as values.
I have managed to do this on a workbook without pivot tables. But I get the following message with this project. I don't want to copy the embedded data for each save as it is crazy slow. Any hints or help?
Option Explicit
Sub JhSeparateSave()
Dim ws As Worksheet
Dim NewName As String
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
' Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
With Application
.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
MsgBox ("Copy step 1")
ws.Copy
With ActiveWorkbook.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewName & "-" & ws.Name
ActiveWorkbook.Close
MsgBox ("Saved sheet: " & ws.Name)
End If
Next ws
End With
End Sub