Split Workbook - improved code required

  • Thread starter Thread starter HamishM
  • Start date Start date
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
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
Thanks Bob,

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

any ideas?
 
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)
 
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
 
Yeah, it's identical.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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?
 
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))
 
Back
Top