Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan

Try Ron's:
http://www.rondebruin.nl/copy6.htm
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Positive" wrote:
Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan

You could run a macro.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Mon, 06 Aug 2007 14:26:13 -0000, Positive wrote:

Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan

On Aug 6, 4:30 pm, Gord Dibben <gorddibbATshawDOTca wrote:
You could run a macro.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP



On Mon, 06 Aug 2007 14:26:13 -0000, Positive wrote:
Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan- Hide quoted text -

- Show quoted text -

Guys,
Thank you so much but I have never used MACRO before; therefore what
you wrote is pretty advanced and foreign to me. Can you pls give me
some instructions which are more for MACRO beginners?

Many thanks
Lan

On Aug 10, 12:04 pm, Positive wrote:
On Aug 6, 4:30 pm, Gord Dibben <gorddibbATshawDOTca wrote:





You could run a macro.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" & w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Mon, 06 Aug 2007 14:26:13 -0000, Positive wrote:
Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan- Hide quoted text -

- Show quoted text -

Guys,
Thank you so much but I have never used MACRO before; therefore what
you wrote is pretty advanced and foreign to me. Can you pls give me
some instructions which are more for MACRO beginners?

Many thanks
Lan- Hide quoted text -

- Show quoted text -

Awesome! Now i figure it out and it does work. Thank you so much for
your help
Lan

"Positive" wrote in message
oups.com...
On Aug 10, 12:04 pm, Positive wrote:
On Aug 6, 4:30 pm, Gord Dibben <gorddibbATshawDOTca wrote:





You could run a macro.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" &
w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Mon, 06 Aug 2007 14:26:13 -0000, Positive
wrote:
Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan- Hide quoted text -

- Show quoted text -

Guys,
Thank you so much but I have never used MACRO before; therefore what
you wrote is pretty advanced and foreign to me. Can you pls give me
some instructions which are more for MACRO beginners?

Many thanks
Lan- Hide quoted text -

- Show quoted text -

Awesome! Now i figure it out and it does work. Thank you so much for
your help
Lan


This has actually helped me as well, thank you.
However, I was wondering how could you change that so the "copied"
worksheets save as *.csv files?

"Mr BT" wrote in message
news:9Ipvi.51008$_d2.4000@pd7urf3no...

"Positive" wrote in message
oups.com...
On Aug 10, 12:04 pm, Positive wrote:
On Aug 6, 4:30 pm, Gord Dibben <gorddibbATshawDOTca wrote:





You could run a macro.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" &
w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

On Mon, 06 Aug 2007 14:26:13 -0000, Positive
wrote:
Please help.

I have a workbook of about 100 worhsheets. How do I detach these 100
worksheets into 100 DIFFERENT WORKBOOKS without copy/paste and save
manually every single sheet so that they can be 100 independent excel
workbooks.

Many thanks

Lan- Hide quoted text -

- Show quoted text -

Guys,
Thank you so much but I have never used MACRO before; therefore what
you wrote is pretty advanced and foreign to me. Can you pls give me
some instructions which are more for MACRO beginners?

Many thanks
Lan- Hide quoted text -

- Show quoted text -

Awesome! Now i figure it out and it does work. Thank you so much for
your help
Lan


This has actually helped me as well, thank you.
However, I was wondering how could you change that so the "copied"
worksheets save as *.csv files?
I figured it out.
I added something to the end of the saveas line
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & w.Name,
FileFormat:=xlCSV, CreateBackup:=False

Good to hear.

Gord

On Sat, 11 Aug 2007 21:45:35 GMT, "Mr BT" wrote:

I figured it out.
I added something to the end of the saveas line
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & w.Name,
FileFormat:=xlCSV, CreateBackup:=False