Split Workbook - improved code reuired

[HamishM]

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

 

[Iridium]

Not sure but I think this may be of some use to you:

http://www.excel-center.com/mailing/064399h.htm

HTH

 

[HamishM]

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]

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]

ADVERTISEMENT

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

 

[Solidfog2]

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]

Dusty

Why not start a new thread?