Saving worksheets to separate files (Excel 97)

Topic

New Reply

I have multiple worksheets located in one file and I’d like to remove or copy each worksheet from the one file into their own separate files. For example file.xls has 5 worksheets named sheet1, sheet2, sheet3, sheet4 and sheet5. I’d like sheet1 to be saved to a new file called file1.xls, and sheet2 to a new file called file2.xls… etc.

Reply | Quote

Replies

Well, my solution isn’t exactly elegant, but what I usually do is simply highlight the entire worksheet by clicking in the upper left corner, right click -select copy. Then create a new workbook and paste the copied sheet in. Then save it as whatever name I like.
I’m fascinated to see how this can be done more quickly and “professionally”.

Reply | Quote

Thank you for your reply, however I should have worded my question a bit differently, as I have about 30 worksheets that I want to save to 30 different files, and I was wondering if there was a fast and quick way to do so without having to do the copy and paste and save as… thanks anyway… I was thinking maybe there was a macro out there or something.

Reply | Quote

LJM,
I put this little ditty together:

Sub Breakout()
‘
‘ BreakOut Macro
‘ removes all worksheets, leaving worksheet #1
‘ All worksheets saved to active directory in their own name.
‘
Dim ShHome As String
ShHome = ActiveSheet.Name

For Each Worksheet In Worksheets
Worksheets(2).Move
ActiveWorkbook.SaveAs (ActiveSheet.Name & “.xls”)
ActiveWorkbook.Close
Next Worksheet
End Sub

It does nothing with the first worksheet in the bunch, but you can save that outside the loop if you want.

[Added after first posting:}
The ShHome variable can help you recognize the “main” worksheet, and prevent it form being moved out, as
If activeworksheet.name=ShHome then exit sub

Errol

Reply | Quote

Thank you, Thank you! That macro worked perfectly!

Now to complicate things a bit more… before saving all the worksheets to their own files, can I have the macro look at each worksheet and adjust the column to width automatically, and do a copy paste special values for each worksheet?

What we have is an ADP download of peoples vacation and flex balances, and we want to email each supervisor their employees vac & flex balances. I’ve used the download from ADP to make a pivot table, I then did a “show pages” function based on the supervisor’s name… this creates the 30 or so worksheets I mentioned in the first request. Since I don’t want everyone to see everyone elses balances I want to copy paste values so the pivot table is no longer a pivot table, plus I want the column widths to be adjusted as well, as some peoples names are longer than others.

Last but not least (I’m having doubt about this one) is there a way to have these newly created files attached to separate emails, going to the correct person based on the file name (the worksheets, and then the new file names based on the worksheet name are the supervisor names). We use lotus notes release 6.

Anyone got any ideas?!?!?!

Thanks so much!!!!!

Reply | Quote

LJM,
For your next two wishes….

I learned lots of stuff by this method:
1. Click on Tools|Macros|Record New Macro
2. Choose to store the new macro in a new workbook (to protect the integrity of your original files)
3. Then do your thing. In this case, select/copy/paste special(values). Select the columns you want to resize, double-click on the column dividers (up near the ABC’s). Then stop the maco.
4. Open the Macro editor to see your new program. There’s the commands you’ll need.

The automatic macro often adds extra commands, but you should be able to figure out what you need.

Seriously, if this just doesn’t stick in your head, or you’d rather learn Senegalese than VBA, let me know. These two things are simple enough to do.

As for the email thing, I bet it can happen, but I’ve never worked on that type of program.

Errol

Reply | Quote

LJM,
For your next two wishes….

I learned lots of stuff by this method:
1. Click on Tools|Macros|Record New Macro
2. Choose to store the new macro in a new workbook (to protect the integrity of your original files)
3. Then do your thing. In this case, select/copy/paste special(values). Select the columns you want to resize, double-click on the column dividers (up near the ABC’s). Then stop the maco.
4. Open the Macro editor to see your new program. There’s the commands you’ll need.

The automatic macro often adds extra commands, but you should be able to figure out what you need.

Seriously, if this just doesn’t stick in your head, or you’d rather learn Senegalese than VBA, let me know. These two things are simple enough to do.

As for the email thing, I bet it can happen, but I’ve never worked on that type of program.

Errol

Reply | Quote

Thank you, Thank you! That macro worked perfectly!

Now to complicate things a bit more… before saving all the worksheets to their own files, can I have the macro look at each worksheet and adjust the column to width automatically, and do a copy paste special values for each worksheet?

What we have is an ADP download of peoples vacation and flex balances, and we want to email each supervisor their employees vac & flex balances. I’ve used the download from ADP to make a pivot table, I then did a “show pages” function based on the supervisor’s name… this creates the 30 or so worksheets I mentioned in the first request. Since I don’t want everyone to see everyone elses balances I want to copy paste values so the pivot table is no longer a pivot table, plus I want the column widths to be adjusted as well, as some peoples names are longer than others.

Last but not least (I’m having doubt about this one) is there a way to have these newly created files attached to separate emails, going to the correct person based on the file name (the worksheets, and then the new file names based on the worksheet name are the supervisor names). We use lotus notes release 6.

Anyone got any ideas?!?!?!

Thanks so much!!!!!

Reply | Quote

LJM,
I put this little ditty together:

Sub Breakout()
‘
‘ BreakOut Macro
‘ removes all worksheets, leaving worksheet #1
‘ All worksheets saved to active directory in their own name.
‘
Dim ShHome As String
ShHome = ActiveSheet.Name

For Each Worksheet In Worksheets
Worksheets(2).Move
ActiveWorkbook.SaveAs (ActiveSheet.Name & “.xls”)
ActiveWorkbook.Close
Next Worksheet
End Sub

It does nothing with the first worksheet in the bunch, but you can save that outside the loop if you want.

[Added after first posting:}
The ShHome variable can help you recognize the “main” worksheet, and prevent it form being moved out, as
If activeworksheet.name=ShHome then exit sub

Errol

Reply | Quote

This should copy each sheet to a new file and name them sequentially :

    Sub SheetsToFiles()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, intIndex As Integer
        For Each ws In ThisWorkbook.Worksheets
            ws.Copy
            intIndex = intIndex + 1
            With ActiveWorkbook
                .SaveAs "File" & intIndex & ".xls"
                .Close
            End With
        Next
    Application.ScreenUpdating = True
    End Sub

The original workbook is left intact.

Andrew C

Reply | Quote

Andrew,
Would this work if I wanted to, say, save three (3) of five (5) worksheets from one workbook/file to one (1) nes workbook/file?
Thanks.

Reply | Quote

Andrew’s code will copy each sheet to a different file, not some of the sheets to one file.

Reply | Quote

Hans,
Do you (or anyone else) know of a code that will copy only selected worksheets from a workbook into ONE new workbook (without having to copy each of the selected worksheets individually)?
Thanks.

Reply | Quote

Yes.

ActiveWindow.SelectedSheets.Copy

Reply | Quote

Hi

Hans solved this problem for me with the the attached code, Create a simple User Form and use this code,

It will allow you to copy 1 or any number of visible worksheets to a new workbook. (Please note the password part of the code you will need to set this to your own password if any!)

Braddy

Reply | Quote

Braddy,

The code you attached is incomplete, moreover, the complete version (from the thread starting at post 558,147) acts differently from what has been discussed here: it copies values only, not the entire worksheet including formulas. It’s up to Jeff Kirk to decide if he can use it.

Reply | Quote

Hi Hans

I apologise If I have made an error here, because I get so much from this forum I was trying to give a little back, I will be more careful in future what I post.

Once again apologies for any misunderstanding.

Braddy

Reply | Quote

This should copy each sheet to a new file and name them sequentially :

    Sub SheetsToFiles()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, intIndex As Integer
        For Each ws In ThisWorkbook.Worksheets
            ws.Copy
            intIndex = intIndex + 1
            With ActiveWorkbook
                .SaveAs "File" & intIndex & ".xls"
                .Close
            End With
        Next
    Application.ScreenUpdating = True
    End Sub

The original workbook is left intact.

Andrew C

Reply | Quote

Thank you for your reply, however I should have worded my question a bit differently, as I have about 30 worksheets that I want to save to 30 different files, and I was wondering if there was a fast and quick way to do so without having to do the copy and paste and save as… thanks anyway… I was thinking maybe there was a macro out there or something.

Reply | Quote

The macros already given in this thread should do the job for a large number of sheets but to make a one off/ ad hoc copy of a sheet then:
click Edit
then click Move or Copy Sheet
and explore the options in the dialog box that appears

You can also get at this feature by right clicking on the sheet name tab.

(not) stuck

Reply | Quote

The macros already given in this thread should do the job for a large number of sheets but to make a one off/ ad hoc copy of a sheet then:
click Edit
then click Move or Copy Sheet
and explore the options in the dialog box that appears

You can also get at this feature by right clicking on the sheet name tab.

(not) stuck

Reply | Quote

Well, my solution isn’t exactly elegant, but what I usually do is simply highlight the entire worksheet by clicking in the upper left corner, right click -select copy. Then create a new workbook and paste the copied sheet in. Then save it as whatever name I like.
I’m fascinated to see how this can be done more quickly and “professionally”.

Reply | Quote