How Do I Save Sheets In A Workbook To Separate Files?

I have a wokbook with MANY sheets. I was wondering if there was a quick way
to save the sheets into separate files (perhaps using tab names) whilst
retaining each ones header and footer.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Combine Worksheets from Multiple Workbooks into One
Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined in ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...

Helpful Excel Macros

Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics


I have a workbook with 45 sheets and I am looking for a script to save all sheets as separate text files. The script should take the "save as" file names from the sheet names.

Although they should be saved as text files, the extension is php as given in the sheet names.

Unfortunately I've been out of practice for years with Excel VBA macros of this deepth, so I would highly appreciate some help for the above!

Thanks a lot, Minnesaenger

I have a workbook that consists of 2 worksheets, after the data is entered
the 2 sheets are separate and saved into a folder with the the 2 sheets as
individual files. This all works fine if I specify the saving location, what
I would like to know is how to automatically save the the new folder and
files into the root directory of the device the original workbook was
started. The workbook runs on several portable usb devices so the starting
location can vary from device to device. The name of the parent folder that
the new folders and files are saved to is constant. ie: ??:\Sheets\??????.
Thank you


Can u help any body in merging the multiple *.xls files into one single *.xls file but each *.xls file com in separate worksheet.

Say i have 30 xls files in datewise i.e., 01.10.10, 02.10.10, 03.10.10 so on....

I want to merge all the above 30 xls files in single file master workbook - in that master workbook file the above 30 xls should come in separate work sheets.

Thank you


i'm trying to loop through a folder of csv files, import them each into a separate workbook, and save as xls files into a different folder using the contents of a particular cell as the name of the xls.

i've found code for loops, but i don't have the expertise to customize for cvs files. and the save-as has been giving me fits.

any help would be appreciated!

I have multiple ".csv" files with different names in c:\temp\ that I want to combine into a single workbook in different worksheets and then save the workbook as "Output"

Is it possible to save a workbook in a macro script or no? If not it's not that big of a deal. Thank you for the help.

(xposted to

Hi all,

I’ve got a reasonably large workbook of around 150 sheets.

I’ve been using Excel 2010 and recently it has started crashing when I try and save it in compatibility mode (.xls). The workbook saves ok in .xlsm or .xlsx format and it will open and save ok in .xls format in Excel 2003. However it will always crash every time I try to save it in .xls format in excel 2007 or 2010.

I have spent 2 days trying to pinpoint the exact cause and seem to have narrowed it down to around 40 sheets. When I delete them all it saves ok. These 40 sheets are required and so I would prefer not to delete them all but instead find the problem with them.

The only differing feature on them is that the header image and footer image go outside of the header and footer margins specified – this does not occur on any other sheet – and it is not what I intended. The error may have been caused by a printer driver communicating the wrong information to excel or formatting the page wrong or something. However I could be way off.

Anyone had any similar issues or could offer some assistance on the matter?

I have also started a thread over at Mr Excel:

and will keep you informed of any progression there.

hi all,

I have a main processing file with say 10 sheets. After processing I want to save selected sheets (1, 4 and 5) as a new excel file. The code I have found so far saves each sheet as a separate file, however, I want to combine the selected sheets in one file.

Any ideas?


can anyone help with a macro to save each worksheet as a separate book
(named as its sheet name) to a given folder (C:\mydocs\myfolder)?

Many thanks


I have a xlsm workbook with 3 worksheets. I've recorded a macro that formats the data in these sheets. I want the file to then be saved as a csv file (which IT picks up to load into an application) and an xlsx file for my records.

How can I save the data in the 3 worksheets as seperate csv files?
How do I save the xlsx file with 3 worksheets in it?
All the column headers are the same. The number of rows having data differs in each sheets.


I usually number my sheets in the header with a &tab &page. Unfortunately, while most of my sheets are in portrait mode, a few perforce have to be printed in landscape to fit. The problem then is that the header prints on the top left of the page as one files it away in a binder and therefore is not visible if you are flicking through the binder, and I would like it to be on the top right as one looks at an A4 sheet in portrait mode.

In other words, when I have a landscaped sheet, I would like there to be a footer in the bottom right. However, as the file will be viewed "normally", ie in portrait mode, I would like the footer to be rotated 90 degrees to the right.

=if(OR(wall=brick,mission=impossible),head=ouch,"Yay team!")
how's that for my first bit of programming?

I'm trying to create a macro to save multiple sheets of data as separate text files but i cannot have the .txt file extension. Any ideas?


I have one master Excel workbook that acts as a log-in (or gateway) file (1MB) and then each passworded link in this file opens up a separate larger workbook for each logged in memeber to use (10MB+ per link (20-30 Links).

I want the user to be able to save (Done through Macros), manipulate and interact with the data in the files BUT I don't want them to be able to see the excel menus and be able to Edit the pages, copy or save the files.

In Excel 2003 there was an 'Add Interactivy' button in the 'Save as a Webpage' file menu that seems to have been removed that would of been perfect for what I was looking for...

I can't use SkyDrive because it keeps telling me that the file exceeds their 5MB 'view online' limit, and it prompts the user to download and save the file (which defeats the object of what I want).

Basically... I want people to type in an address, for it to open an Interactive log-in workbook. For them to use the Log-in workbook to open their own personal Interactive workbook or 'Profile' and be able to Interact with it at SHEET LEVEL ONLY. (No menus, no editting, no copying, so saving etc.)

I've created all of the Excel files I need to compele this task (offline) and it works perfectly- I just need it to work online!

The files are Xlsm files and over 10MB.

Thanks in advance for any help!


I am using Excel 2007. I have a workbook with over 50 sheets in it. I want to see if I could make all of the worksheets hidden from view unless there is a password that will be entered, in order to view the corresponsing sheets. I also need to be able to have the sheets hidden again once the file is saved ( or on close ). Not everyone will have access to the same sheets. Only one person ( aside from myself ) will have access to all of the sheets.

Is there a way to assign individual passwords for each separate user so that I can have the user enter their password to reveal the sheet(s) that they will be working on?


I am having trouble getting all sheets in the workbook to print the same. I
have tried selecting all sheets and setting up my header and footer, margins,
etc. But, when I then go to print preview, only the first sheet in the
workbook has held the settings.

Hello all. I am working on a project that is two part. I am working with Excel 2007. I have a workbook that has 50 or so worksheets that I need to split and save as individual files. I found the following code online. It works fine, however, I need the "new" workbooks to be saved as Excel 97-2003 files. What do I need to add to this to make that happen?


Sub splitsheettoworkbook()

Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Worksheet 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Excel Worksheets\" 'Change this to suit your needs

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.

Application.ScreenUpdating = True

Exit Sub

ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."

End Sub

Part Two is this: I have another file that I'm needing to split as well. However, the information in this workbook is organized in a Pivot Table. I have utilized the 'Show Report Filter Pages' to have the information on separate worksheets so that I can split the file. Will the above code (with the modification to save in 97-2003 format) work with the Pivot Table?


I spent a long time creating a perfect header and footer. Now i need it in another sheet within the same workbook.

How would I copy it across, retaining it exactly as it is?




I am trying to drag a bunch of text files into an Excel workbook. I would like each text file to be its own worksheet inside a single workbook. When I click and drag files over into Excel, it opens a new workbook for each file. Is there a way to drag them into a single workbook as separate sheets rather than individual workbooks?


I have a report I create weekly that is about 50,000 lines. It is requested
that they be subtotalled by one of the columns (making about 30 subtotals).
I then have to make a separate sheet for each of these 30 subtotals within
the workbook and paste the contents of each subtotal in each sheet(still
leaving the master totals page). I have to create one of these for 03 04 05
and 06 each week so I need to know if there is a way to subtotal to separate
sheets because copying and pasting is where all my time is spent. Thanks!

I have an .xlsm file that I saved last night at 4:30pm today any time i try to save the file it auto crashes.

I cant save it on the network or locally, I can't save it as a dif file type it all causes the crash.

Other files are fine its just this one.

I have tried moving all sheets to a dif file still crashes..

Any ideas?? I will lose days of work if I can't get this file to work!

I am using excel 2007. I want to convert each sheet into a separate pdf file. If i select multiple sheets and convert, it converts all of them into one file. I have 20 sheets in a file. Right now I am converiting them manually one at a time. I dont have adobe and am a VBA novice. Any suggestions? Appreciate your help.

Hi everybody.

I need generic code to add a new workbook & save it using a filename format & for some reason the code below is not working.

What I am trying to do ultimately is, from a master annual workbook,
add a new workbook to a subfolder named after the relevant month (03 for March etc) & save to that folder as "DC" & mm (march would be DC03). This file is known as the monthly master as there could be up to 10 CSV files per month to be consolidated into the master monthly.

I then do other things with these montly files to consolidate into annual file but for the moment I have this key problem & this is what I have


Set NewBook = Workbooks.Add
            With NewBook
                .Title = "DC08" & mnth
                .Subject = "DC"
                .SaveAs Filename:="C:\Documents and Settings\New Owner\My Documents\AT\BNZ TransactionsHist\08DC\" _
                & mnth & "\" & "DC08" & mnth
            End With

It won't save the file to the folder I am trying to nominate by user input called "mnth". So I want create a master file for the month & save that file in the monthly folder.

My code works as separate operations but there is still a bit of automation I can do as one coding operation.

I also need this as I have 2 other operations I do monthly that will use this code.

Cheers for any help

I have testing data that I commonly need to separate out into different teachers and schools. I want to separate the file into separate sheets using criteria from a given column, and then move each sheet to a new book. (I often have to do some variation of this with a variety of files... I imagine this is a common task.) I've found the following code that does most of what I want, except for a couple things as mentioned below.

Please Login or Register  to view this content.

Goal #1 - it only copies one row of my header, but my header usually takes 6 or 7 rows, all of which should be copied as is into each new sheet.
Goal #2 - I'd like it to also move each sheet to a new book

Any assistance on accomplishing these goals would be greatly appreciated. Samples are attached and linked below. Thanks!

Attachment 341533 (simple workbook)
Attachment 341534 (macro-enabled with above code) (macro-enabled with the above code) (simple workbook)

I'm running Excel 2007 on a WinXP (SP2) machine (dual core, 4GB RAM, 8GB free HDD space).

I keep getting a strange error message frequently when I try to save large files (> 2MB) with lots of sheets. Excel seems to save okay (the save progress bar at the bottom finishes), but then a dialog box pops up saying:

Your change could not be saved to filename.xls because of a sharing violation. Trying saving to a different file.

Once I hit Ok on that dialog box, it immediately gives another dialog:

The file you are trying to open, '56C2F10', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? Y/N

I hit Yes, and it then opens another version of the same file and gives me the Save As... dialog box. Once I enter a different name, it saves that file and closes it, leaving me with the original file that I was trying to save (but could not). I then close that original file and open the new/renamed file and it contains all my changes.

As I mentioned, this happens usually with large files (>2MB), usually ones which have many sheets (>10). It has happened with .xls, .xlsx, and .xlsm formats. I am saving only to my local hard drive, and the file is not shared with/on any other user/server/machine.

It is very annoying, since I have to interrupt my work to close the "corrupted" file and open the newly renamed one, not to mention that I end up with multiple copies of the files.

Any insight as to what's causing this problem, and how to fix it?

I am setting up a spreadsheet to analyze data from a program I use. The files are with extension .npe, but to upload it, I added the .txt.

I have the following worksheets: Input, Run Selection, Summary, and R1 through R20.

On the input tab are the path file locations, starting in C6 and down to C25 (up to 20 files). The paths are from another workbook, and brought in automatically to the Input spreadsheet.

I want to write a VBA code that reads the info in C6-C25, and writes the text from those files into sheets R1-R20. However, there may be 2 files, 13 files, or 20 files.

I have most of the code written, but I am having the hardest time figuring out how to read in each of the files and put them into the individual sheets. I have found code that comes close, but the issue is that the code is really extensive, and I'm not sure I need most of it.

I have attached a sample text file, and the coding I have so far. Any assistance would be appreciated.


I am looking for help with some code to Move 3 sheets (all with formulas) into a new workbook. Workbook needs to then be saved as "DSR_DDMMYYYY".xlsx Press save again (this will refresh the formulas which use an addin to query a database) Select all sheets Select all Copy, Paste Values Press save again Initiate another macro to email book Anyone willing to give this a go?