Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS

  • Thread starter Thread starter Positive
  • Start date Start date
P

Positive

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








- Show quoted text -
Click to expand...

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
 
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 -
Click to expand...

Awesome! Now i figure it out and it does work. Thank you so much for
your help
Lan
 
Positive said:
Awesome! Now i figure it out and it does work. Thank you so much for
your help
Lan
Click to expand...


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 said:
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?
Click to expand...
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
 
Back
Top