Copy worksheets to new workbooks incl. Pivot Table

O

OBMIKEE

New Member
Joined
May 24, 2017
Messages
8
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 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
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Welcome to the forum.

Which line causes the error? (I assume you are actually doing more sheets than you have listed there)

Please note that I deleted your duplicate posting of this question.
 
Upvote 0
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Causes the error

Yes - there are many more sheets in the workbook
 
Upvote 0
You can do this instead:

Code: 
activesheet.usedrange.copy
activesheet.usedrange.pastespecial xlpastevalues

Note that you will lose the pivot table formatting.
 
Upvote 0
Thanks - unfortunately the behaviour of adding this in the code is as follows;

Creation of a new workbook STRATEGY (being the first in the range).
This new workbook contains 'all' of the other worksheets.

The intended behaviour is to create a seperate workbook for each of the worksheets.

Any thoughts?
 
Upvote 0
There is no way that the code I posted alters the way your loop copies sheets.
 
Upvote 0
It no longer gets to the loop with this code added.

The behaviour appears to paste special values in source wb, then saves entire wb as - with the name of first ws.
Workbook closes, no longer loops.

The result leaves me with Source wb
A new wb named 'Strategy' with Strategy ws results as expected (ie Paste Values for Pivot table), however all other ws in wb contain pivot tables and formatting as per sourve wb.
 
Upvote 0
What is your final code? You must have done far more than replace the error line with those two lines.
 
Upvote 0
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)).UsedRange.Copy
Sheets(sheetnames(i)).UsedRange.PasteSpecial xlPasteValues
Path = "C:\Users\obriemik\Desktop\Mike\Mike1"
Filename = sheetnames(i) & Range("G1") & Range("F1")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close

Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to loop through sheets and copy to new workbook including the pivot tab
Replies
0
Views
178
simont1975
S
C
  • Solved
Copy only required cells to new workbook
Replies
5
Views
100
MARK858
MARK858
R
  • Question
VBA to Copy Multiple sheets to new workbook & save as .xlsb or .xlsx
2
Replies
16
Views
626
rahildhody
R
The Ruff Report
  • Solved
VBA - replace a character in a range to save
Replies
5
Views
166
RobP
R
P
  • Question
Copy method of worksheet class failed" with pivot table
Replies
4
Views
287
programsam
P

Forum statistics

Threads
1,214,520
Messages
6,120,017
Members
448,936
Latest member
almerpogi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top