Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Save sheet?
How can i save 200 sheets in an excel file as 200 excel file with name of
sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#2
|
|||
|
|||
Sub SaveSheets()
Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#3
|
|||
|
|||
Bob's code not close the new workbooks.
Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#4
|
|||
|
|||
Easily fixed
Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Activeworkbook.Close Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ron de Bruin" wrote in message ... Bob's code not close the new workbooks. Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#5
|
|||
|
|||
Hi Bob
Easily fixed I know<g -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Easily fixed Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Activeworkbook.Close Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ron de Bruin" wrote in message ... Bob's code not close the new workbooks. Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#6
|
|||
|
|||
Hi Ron,
I know you know. I read your page :-) Bob "Ron de Bruin" wrote in message ... Hi Bob Easily fixed I know<g -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Easily fixed Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Activeworkbook.Close Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ron de Bruin" wrote in message ... Bob's code not close the new workbooks. Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#7
|
|||
|
|||
Perfect - and a great thanks from Norway!!
"Ron de Bruin" wrote: Bob's code not close the new workbooks. Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
#8
|
|||
|
|||
Well - it looks like you both know - anyway, thanks for great support in
short time notice. I had a bad feeling i had to manually cut, past, name and save them. Would have taken loooong time. "Bob Phillips" wrote: Hi Ron, I know you know. I read your page :-) Bob "Ron de Bruin" wrote in message ... Hi Bob Easily fixed I know<g -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Easily fixed Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Activeworkbook.Close Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ron de Bruin" wrote in message ... Bob's code not close the new workbooks. Maybe what you want ? Here is a example that create a folder with the new workbooks http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Bob Phillips" wrote in message ... Sub SaveSheets() Dim i As Long With ThisWorkbook For i = 1 To .Worksheets.Count .Worksheets(i).Copy ActiveWorkbook.SaveAs Filename:=Worksheets(i).Name & ".xls" Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Heine" wrote in message ... How can i save 200 sheets in an excel file as 200 excel file with name of sheet as file name. Due to the number of sheets it is needed to be done automaticly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save a single sheet from a workbook | Excel Discussion (Misc queries) | |||
how to let excel ask to save the sheet when closing the excel file | Setting up and Configuration of Excel | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Can't save chart as object in current sheet | Charts and Charting in Excel | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |