Split Workbook - improved code reuired

HamishM

New Member
Joined
Feb 5, 2004
Messages
16
Hi,

I am using the following code to split one workbook with multiple worksheets into - many workbooks.


Sub SplitSheets()
Dim W As Worksheet
For Each W In Worksheets
W.SaveAs ActiveWorkbook.Path & "/" & W.Name
Next W
End Sub


The problem i have is this takes several minutes to run and also produces a file size which appears significantly too large (e.g approx 2MB when the original is only a few hundred k).

Could i ask for some help optimising the code please!

thanks,
Hamish
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

HamishM

New Member
Joined
Feb 5, 2004
Messages
16
Re: Split Workbook - improved code required

Thanks - that sort of does the trick.

Sub SheetsStoring()
Dim dName$
Dim Switch
ActiveWorkbook.Windows(1).SelectedSheets.Copy
While Switch = False
dName = InputBox("Filename=")
If dName = "" Then Exit Sub
If Dir(dName) <> "" Then
Beep
If MsgBox("Already exists, new Name?", _
vbCritical + vbYesNo) = vbNo Then Exit Sub
Else
ActiveWorkbook.SaveAs dName
Switch = True
End If
Wend
End Sub

I would like it to create a new workbook for every single worksheet - not just selected and save them into the same directory as the master.

Any thoughts or changes please?

thanks,
Hamish
 

donalde

Board Regular
Joined
Feb 27, 2002
Messages
77
Saving sheets in their name is quite easy:

Sub save_sheets_into_workbooks()
Application.DisplayAlerts = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
sh.Copy
ActiveWorkbook.SaveAs FileName:=sh.Name
ActiveWorkbook.Close
Next
Application.DisplayAlerts = True
End Sub

Idea is to copy active sheet as a new workbook, then save it with same name as sheet is. Then close active (just saved) workbook, and return to original, where next sheet is handled in same way.
 

Solidfog2

New Member
Joined
Oct 17, 2005
Messages
8

ADVERTISEMENT

In reference to this last post, how can I change the location of where they're being saved. . . say a desktop folder?
 

Solidfog2

New Member
Joined
Oct 17, 2005
Messages
8
and in addition, I'd like to append something else. I like naming it a specific sheet. . . but I'd like to name it A13 + Sheet.Name. where A13 is the contents of cell A13. But only the A13 in the first worksheet. Wow, I'm annoying! Sorry! Thanks in advance. I love you.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Dusty

Why not start a new thread?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,333
Messages
5,601,008
Members
414,421
Latest member
tonybear1994

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