|
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOO
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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
"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? |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
"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 |
Detach different WORKSHEETS in one WORKBOOK into DIFFERENT WORKBOOKS
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 |
| All times are GMT +1. The time now is 11:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com