Split Workbook - improved code required

H

HamishM

Hi,

I am using the following code to split one workbook with multipl
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 it doesn'
actually work - it replicates the original.

Could i ask for some help optimising the code please!

thanks,
Hamis
 
B

Bob Phillips

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Hamish,

Give this a try

Dim W As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each W In Worksheets
W.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "/" & W.Name
activeworkbok.Close
Next W
Application.DisplayAlerts = True
Application.ScreenUpdating = True


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

HamishM

Thanks Bob,

Seems to work alright for the first three sheets then runtime error
occurs on the copy of worksheet.

any ideas?
 
B

Bob Phillips

Hamish,

I just tried it with 7 worksheets, and all 7 were saved fine.

Nothing off the top, I'll post back if I think of anything.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

HamishM

Bob,

this is the code i'm using.....

Sub SplitSheets()
Dim W As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each W In Worksheets
W.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "/" & W.Name
ActiveWorkbook.Close
Next W
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Su
 
B

Bob Phillips

Yeah, it's identical.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

HamishM

I've now tried it on several other workbooks and it seems to be working
No idea what was wrong with the other but am happy now!

Just another thought - if i only wanted select sheets to be split out
can i create a list in the code? (or select sheets and then run th
macro?
 
T

Tom Ogilvy

Sure

change
For Each W In Worksheets

to
for each W in Worksheets(Array("sheet1","Sheet3", _
"Sheet5","sheet7"))

or

for each W in Worksheets(Array(1,3,5,7))
 
Top