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

--
Message posted from http://www.ExcelForum.com

--

HTH

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

"HamishM " wrote in message
...
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 it doesn't
actually work - it replicates the original.

Could i ask for some help optimising the code please!

thanks,
Hamish


---
Message posted from http://www.ExcelForum.com/

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)

"HamishM " wrote in message
...
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 it doesn't
actually work - it replicates the original.

Could i ask for some help optimising the code please!

thanks,
Hamish


---
Message posted from http://www.ExcelForum.com/

Thanks Bob,

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

any ideas?


---
Message posted from http://www.ExcelForum.com/

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)

"HamishM " wrote in message
...
Thanks Bob,

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

any ideas?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Yeah, it's identical.

--

HTH

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

"HamishM " wrote in message
...
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 Sub


---
Message posted from http://www.ExcelForum.com/

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?

--
Message posted from http://www.ExcelForum.com

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))

--
Regards,
Tom Ogilvy


HamishM wrote in message
...
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 the
macro?)


---
Message posted from http://www.ExcelForum.com/