Macro to split a workbook

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
I need help with VBA code to split a workbook and save each individual worksheet as a new workbook. I have XL 2010, but others who will use this file have 2007. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Code:
[FONT=Arial]Below is the code I have so far:<o:p></o:p>[/FONT]
[FONT=Arial][FONT=Arial]   Dim sht As Worksheet<o:p></o:p>[/FONT]
[FONT=Arial]    Dim MyCurrPath As String<o:p></o:p>[/FONT]
[FONT=Arial]    MyCurrPath = ThisWorkbook.Path<o:p></o:p>[/FONT]
[FONT=Arial]    ainput = InputBox("Enter Date for this report")<o:p></o:p>[/FONT]
[FONT=Arial]    For Each sht In ActiveWorkbook.Worksheets<o:p></o:p>[/FONT]
[FONT=Arial]        sht.Copy<o:p></o:p>[/FONT]
[FONT=Arial]    ActiveWorkbook.SaveAs Filename:= _<o:p></o:p>[/FONT]
[FONT=Arial]    MyCurrPath & "\" & sht.Name & " OTC Report " & ainput & ".xlsm" _<o:p></o:p>[/FONT]
[FONT=Arial]    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False<o:p></o:p>[/FONT]
[FONT=Arial]    ActiveWindow.Close<o:p></o:p>[/FONT]
[FONT=Arial]    Next sht<o:p></o:p>[/FONT]
[FONT=Arial]End Sub<o:p></o:p>[/FONT]
[/FONT]
The code works unitl the last sheet is copied. Then I get a Runtime Error ‘1004’ Method ‘Copy of Object’_Worksheet failed.<o:p></o:p>
I’m a macro newbie & would appreciate any advice on where I’ve gone wrong.<o:p></o:p>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
Could change it to:
Rich (BB code):
Sub CopyThing ()
Dim i as Long
Dim MyCurrPath As String<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
MyCurrPath = ThisWorkbook.Path<o:p></o:p>
ainput = InputBox("Enter Date for this report")<o:p></o:p>
For i = 1 to activeworkbook.worksheets.count
 Sheets(i).Copy<o:p></o:p>
 ActiveWorkbook.SaveAs Filename:= _<o:p></o:p>
 MyCurrPath & "\" & sheets(i).Name & " OTC Report " & ainput & ".xlsm" _<o:p></o:p>
 , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False<o:p></o:p>
 ActiveWindow.Close<o:p></o:p>
Next i
End Sub
<o:p></o:p>

 

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
Thanks so much for your very prompt reply! I've tried your code & the first sheet is copied & saved. Original file left untouched, so that's good. Then Sheet 2 is copied but not yet saved. I get a new error message: "Runtime Error 9: Subscript out of range" Code breaks here: ActiveWorkbook.SaveAs Filename:= _
 

BarbK

New Member
Joined
Jan 21, 2010
Messages
21
Thanks so much for your very prompt reply! I've tried your code & the first sheet is copied & saved. Original file left untouched, so that's good. Then Sheet 2 is copied but not yet saved. I get a new error message: "Runtime Error 9: Subscript out of range" Code breaks here: ActiveWorkbook.SaveAs Filename:= _
Bump
Does anyone have another idea? I've tried the suggested solution and now get a Runtime Error.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,685
Messages
5,597,531
Members
414,152
Latest member
ReservoirDodds

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
Top