Q:sheet to file

[Guest]

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.

 

[Ron de Bruin]

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

 

[Guest]

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]

Hi Jim

Do you have one column with the path.filename and the other with the sheet
index number of the sheet that you want to save as a seperate workbook from
that file

Do I understand you correct ?

 

[Guest]

Yes. I have my main file, which has sheet1, sheet2, ... in it. Now I have
another file SheetList which has two columns and keeps a number for each
sheet name. So A1:Sheet1 B1:1000, A2:Sheet2 B2:1001,.... are in SheetList.
Now if I am in Sheet1 in my main file, I need to find corresponding number
for sheet1 which is 1000 in our example and save this sheet as 1000.xls.