saving multiple sheets as separate files

[harisaaa7]

Hi,

I am excel 2000 user. I have a workbook with 15 worksheets. Each of the
sheet correspond to an employee and contains some information. I would
like to create 15 separate files from each of the sheet, so that i can
send them to each employee. Is it possible to do this automatically ?

regards
hari

 

[John Thow]

Hi,

I am excel 2000 user. I have a workbook with 15 worksheets. Each of the
sheet correspond to an employee and contains some information. I would
like to create 15 separate files from each of the sheet, so that i can
send them to each employee. Is it possible to do this automatically ?

regards
hari

You can use an Excel VBA macro to do this. With a _copy_ of your workbook
open, play around with the macro recorder to find out how. (Tools; Macro;
Record New Macro) For starters you will want to create a new workbook, open
the window with the original workbook, copy a whole worksheet, switch windows
to the new workbook, paste the copied sheet to it and then save the new
workbook with some appropriate name. Now stop the recorder and see what
you've got. Once you've worked out how to do it for one sheet, you can modify
the macro to do all the sheets.

For hints & tips on Excel macros, see Dave Mcritchie's page at:-

http://www.mvps.org/dmcritchie/excel/getstarted.htm

HTH

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[George Nicholson]

Here is some code I use, but I only need to save 3 of the 20 sheets in this
particular workbook. I have revised it to suit your situation. In your
case it seems that the Select Case statement is unnecessary since you want
to copy all the sheets. I have left it in place in case your needs change.

Sub SaveWorkbookAsSingleSheets()
'Purpose: Save copies of specific sheets as single-sheet workbooks (xl4
format).
' New workbook files will have the same name as the "source" sheet so
make sure they have legal filenames.
' Change the Select Case statements as needed. It is currently set to
save every worksheet in the book individually.
' Code makes note of the original workbook name and changes it back
(via SaveAs) when done.
' It is strongly encouraged you work with a disposable copy of the
workbook in any case.

Dim strOriginalName As String
Dim lngOriginalFormat As Long
Dim wkb As Workbook
Dim wks As Worksheet
Dim w As Integer

strOriginalName = ThisWorkbook.Name
lngOriginalFormat = ThisWorkbook.FileFormat
For w = 1 To ThisWorkbook.Sheets.Count
Set wks = ThisWorkbook.Sheets(w)
Select Case wks.Name
Case "LossFactors", "TCLog1", "TCLog2"
' Do nothing

Case Else
' Save a copy in a single-sheet file
ActiveWorkbook.SaveAs Filename:=wks.Name & ".xls",
FileFormat:=xlExcel4
End Select
Next w
' Save the workbook again with it's original name and normal format.
' (SaveAs usage changes the Active workbook name and we DON'T want the
current name to persist)
ActiveWorkbook.SaveAs Filename:=strOriginalName,
FileFormat:=lngOriginalFormat

End Sub

Hope this helps,

 

[George Nicholson]

Correction:
During composition one line of code was omitted, as noted below.

The code still runs without it but all you'll get is multiple copies of
whatever the current sheet is when the code is first run.

--
George Nicholson

Remove 'Junk' from return address.

Here is some code I use, but I only need to save 3 of the 20 sheets in this
particular workbook. I have revised it to suit your situation. In your
case it seems that the Select Case statement is unnecessary since you want
to copy all the sheets. I have left it in place in case your needs change.

Sub SaveWorkbookAsSingleSheets()
'Purpose: Save copies of specific sheets as single-sheet workbooks (xl4
format).
' New workbook files will have the same name as the "source" sheet so
make sure they have legal filenames.
' Change the Select Case statements as needed. It is currently set to
save every worksheet in the book individually.
' Code makes note of the original workbook name and changes it back
(via SaveAs) when done.
' It is strongly encouraged you work with a disposable copy of the
workbook in any case.

Dim strOriginalName As String
Dim lngOriginalFormat As Long
Dim wkb As Workbook
Dim wks As Worksheet
Dim w As Integer

strOriginalName = ThisWorkbook.Name
lngOriginalFormat = ThisWorkbook.FileFormat
For w = 1 To ThisWorkbook.Sheets.Count
Set wks = ThisWorkbook.Sheets(w)
Select Case wks.Name
Case "LossFactors", "TCLog1", "TCLog2"
' Do nothing

Case Else
' Save a copy in a single-sheet file

[THE MISSING LINE:]
wks.Activate

ActiveWorkbook.SaveAs Filename:=wks.Name & ".xls",
FileFormat:=xlExcel4
End Select
Next w
' Save the workbook again with it's original name and normal format.
' (SaveAs usage changes the Active workbook name and we DON'T want the
current name to persist)
ActiveWorkbook.SaveAs Filename:=strOriginalName,
FileFormat:=lngOriginalFormat

End Sub

Hope this helps,