Q:sheet to file
 

Hello,
In my excel workbook I have many sheets. I want to save each sheet to a file
with the same name. How can I do this in a module?
Thanks,
Jim.

Q:sheet to file
 

Try this one Jim
Change "C:\" to the path you want

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"JIM.H." wrote in message
...
Hello,
In my excel workbook I have many sheets. I want to save each sheet to a
file
with the same name. How can I do this in a module?
Thanks,
Jim.

Q:sheet to file
 

Ron,
This is really good thanks for this. I need a little bit modification. I
actually need to get file name from another excel file. I have an excel file
called SheetList that keeps all sheet name and a number for each sheet name
(so SheetList has two Columns: SheetName and SheetNumber). I should get
SheetNumber from SheetList for the current sheet and save file as
"SheetNumber.xls" and do this for each sheet. How can I do this?
Thanks,
Jim.

"Ron de Bruin" wrote:

Try this one Jim
Change "C:\" to the path you want

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"JIM.H." wrote in message
...
Hello,
In my excel workbook I have many sheets. I want to save each sheet to a
file
with the same name. How can I do this in a module?
Thanks,
Jim.