Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS

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
 
G

Gord Dibben

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
 
P

Positive

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 -

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
 
P

Positive

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
 
M

Mr BT

Positive said:
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?
 
M

Mr BT

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top