Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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.

View Answers     

Similar Excel Tutorials

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 ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial showing you how to make a user enable macros in a workbook in order to view the workbook. This is very im ...

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 CSV that I need to break up into separate files based on the values in column "G"

My end goal is to break up the data like this:

value A.csv
value B.csv
value n.csv

So all rows with "value A" in column "G" will be saved into value A.csv and so on.

I currently have a macro that will save all open sheets to .csv files.

A macro to break out the data into separate sheets or one that just directly saves the data to csv files would be very helpful.

Any suggestions?

Here is my macro that saves all sheets to .csv files:


Option Explicit
Sub mysaver()
Dim counter As Integer
counter = 1
' counter is for the number of sheets in the workbook
Do While counter

I have a spreadsheet with 10 sheets. I need to set up some VBA code so the macro will save three of the tabs as separate .csv files for uploading into our software.

The three tabs are named: EID Upload, Wages with Locals Upload, Wages without Local Upload.

Right now, I would like the new file names to match the sheet names.



Hello, Can anyone help me with these, please?from the attached file I need to perform 2 actions.

1. save individual files with cost code name.
2. new created files should have a individual sheets by expense code and all data should be there.

For example after performing macro from a large amount of data I should end up with file called : Rosewood/Sandalwwod Sq that contains 3 separate sheets such as Service charges, Voids available and Management fees retirement with all data.



Hi Guys,

I'm using a For Each Loop to loop over my master workbook and save each sheets to a new workbook. This is all working fine, but what I need help with is I'd like to save two sheets where the names are similar to the new workbook.

Here is my basic code I've kindly taken off-line and adapted slightly....

Please Login or Register  to view this content.

So if my master workbook had say for example 10 worksheets... two of them had names like test1, test2 it would save both of these to the same new workbook. All the others would have separate workbooks as their names are nothing like one another,

Any help would be much appreciated.


How can i Save in separate files the sheets of my worksheet, without being
one by one...

S Bárbara

Hi! I have a workbook that had several worksheets in it. Someone else
wanted these sheets as separate .xls files (workbooks). I think it is
easier to have one workbook...simpler for me to update. the problem is the
users only care about individual sheets and want those in separate .xls
files so they only see their pertinent info.

In other words, i have myMasterList.xls which contains Sheet1 through
SheetN. I'm also maintaining separate workbooks called Sheet1.xls,

is there an easy way for me to maintain one workbook, or separate worksheets
that are updated through my myMasterList.xls?

Summary...the problem is I'm updating the stuff twice--once in
myMasterList.xls and again in the Sheet#.xls files.

Any help would be greatly appreciated. Thanks.


Hello -

I export a excel file (master) from a website and that excel file has at least 25 sheets. My client has asked me to provide 25 separate workbooks instead of one WB with 25 sheets in it. I can do manually but to do this in a daily basis can be time consuming for me and therefore I am requesting your help. Is there a solution so that when I run it (template) it will break the master file into separate files? Also, If I can save those individual files in a specific folder that will be great as well. Please suggest as what is possible. Thank you for your help!!

I have a sheet where column A has the names of different managers I need to distribute data to.

I would like a macro to filter by manager name, create a separate sheet with all the data on it, format it the same way and save the sheets in separate files.

Not only would I like the text to be formatted the same way, I would like the sheet itself to have the same page layout.

Anyone have one handy?

I need a macro to copy all sheets in a workbook , Each sheet to one separate workbook.

But I want this workbook to have the header and footer of my old workbook too.

Hello everyone,

Here is what I'm trying to do:
I want to open all the .csv files in a directory, but rather than open them in separate workbooks, I'd like to open each of them in a separate sheet in the same workbook. I should mention that the number of files that will be in the directory will vary from week to week.
What I would then have to do is scan through each worksheet and delete a number of specific rows. Once that is done, I will have to copy the remaining data into the main sheet so I can count the number of unique rows.

Here's some code I came across that will open all files in a directory, but it opens each one in a separate workbook.


Dim i As Integer, wb As Workbook
With Application.FileSearch
.LookIn = "H:\My Documents\Spreadsheets\KB Page Access Report Generator\Test Data"
.SearchSubFolders = False
.Filename = "*.csv"
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'I don't need the code below so I've just commented it out for now.
'Perform the operation on the open workbook
'wb.Worksheets("sheet1").Range("A1") = Date
'Save and close the workbook
'On to the next workbook
Next i
End With

Dear Sir,
I have a sheet in which A column have some names In from B column to D column have data. I want to create sheets as per names in A column and all the sheets will be saved with data in my C drive with separate files. For ex. If there is 5 names then I need 5 separate files with the name of A column. I need to send those persons by mail.


I have a workbook with dozens of sheets. I would like to save each sheet as a separate csv file, with each file taking the name of the worksheet. Is there an easy way to do this? I don't really know VBA.


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

Hi guys

I have about 300 graphs that I want to save as picture files and was wondering if there was a quick way of doing it - They are in 32 sheets which each having about 10 graphs per sheet - is there an add in that I could download.

The thought of copy and paste into paint and then saving as is not something that I want to contemplate just now!



I am having trouble figuring out a way to take 2 separate files with 1 sheet each to be combined into a master file with 2 sheets. Ideally the files would be in separate folders but they could be put in the same file if it would be easier.

I have very basic VBA knowledge/experience and its giving me a headache! Any help would be appreciated!

I have about 60 sheets with different names(not named sheet 1,2,3... but say aaa,bbb,ccc...) in a single workbook. I would like to save every 3 sheets as a separate workbook. The filename should be the name of the first of the three sheets.
For Eg: Sheets aaa,bbb,ccc as workbook aaa,sheets ddd,eee,fff as workbook ddd and so on(total of 30 workbooks) . I have put together a VBA code by combining sections of codes from different threads in this forum

Sub WorksheetLoop() 
    Dim WS_Count As Integer 
    Dim ws1 As Worksheet 
    Dim ws2 As Worksheet 
    Dim ws3 As Worksheet 
    Dim I As Integer 
     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count 
     ' Begin the loop.
    For I = 1 To WS_Count 
         ' store worksheets as variables
        wname = ActiveWorkbook.Worksheets(I).Name 
        Set ws1 = ActiveWorkbook.Worksheets(I) 
        I = I + 1 
        Set ws2 = ActiveWorkbook.Worksheets(I) 
        I = I + 1 
        Set ws3 = ActiveWorkbook.Worksheets(I) 
         ' copy worksheet objects ws1,ws2 & ws3 to a new book and save
        application.workbooks.copy (array(ws1,ws2,ws3)) 
        ActiveWorkbook.SaveAs Filename:=ws.Name & ".xlsx" 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

A syntax error is thrown at the line "application.workbooks.copy (array(ws1,ws2,ws3))" .Please help debug this or create an alternate code for this.

I have a workbook with mutliple sheets in it. Is there a simple way to save each sheet as a separate file.


Hi All,
In my everyday work, I need to check the header and footer of every worksheet in an excel workbook and then edit them. In case of big workbooks having many worksheets, this becomes a tedious process. Does someone know of a macro that can do either of the following:
1. Print all the header and footer from all the worksheets in a workbook as a textfile (log file).
2. Detect the sheets that have the header and footer and ignore the rest so that I can run a macro on these selected sheets to delete the header and footer (I already know of one such macro).

Thanks in advance,

Hello everyone,

I want to run a macro for a text to columns in all the sheets I have in my workbook. The number of sheets I'm using can go up to 60. The below code I found on the internet is only doing the work for the first sheet. It does display the other sheets' names but doesn't perform the text to columns command.

After I have all the sheets as I want them, I eventually would like to save each sheet as a separate excel file.

Is any of you able to help me on that? I.e. first get my macro to work in each sheet and then save the sheets as individual excel files.


Sub WorksheetLoop() 
    Dim WS_Count As Integer 
    Dim I As Integer 
    WS_Count = ActiveWorkbook.Worksheets.Count 
    For I = 1 To WS_Count 
        Selection.TextToColumns _ 
        Destination:=Range("A1"), _ 
        DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, _ 
        ConsecutiveDelimiter:=False, _ 
        Tab:=False, _ 
        Semicolon:=True, _ 
        Comma:=False, _ 
        Space:=False, _ 
        Other:=False, _ 
        MsgBox ActiveWorkbook.Worksheets(I).Name 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines


So - I need to save 2 of the sheets within my workbook as CSVs

Sheet3.SaveAs "C:\Temp2.csv", xlCSV

is ok - but it changes the sheets name within my workbook, aas well as the main worknook name. SDO next time I run the macro, it can't access sheet2 or 3.

How can I get it sio that I get my 2 csv files, and no changes to the naming witin the workbook?

I know theres a way to do this, but I cant figure out how to get it to work automagically.

would it be a macro in a third file?

this is a daily procedure that is a minor PITA, and somewhat time consuming.

as an example: todays master file has 7 sheets. names are column 1, with 3 rows of header labels
sheet one: 115 names
sheet two: 37 names
sheet three: 3488 names
sheet four : 70 names
sheet five : 1771 names
sheet six : 1771 names
sheet seven 1771 names

second file has 70 names on one sheet, names are column 2, one row of header info.

the same names appear in several sheets on the master file.
(example: sheets 5,6&7 are often identical)

the goal is to automate the comparison of the second file (only has one sheet) to the first file (all sheets) looking for the same name.

any names existing in BOTH files should ONLY be highlighted in the second file.

save revision of second file with current date ammended to file name, then do the same thing tomorrow.

any ideas on where I should start?

Thank you for any suggestions!


I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by using
script. Can anyone help?

This site has got me hooked on using VB ... Thank you!! Usually I can find my answers in the archieves, but today I am having trouble. Hopefully someone can help with this problem.
I have a workbook that is used by several people. To maintain its size I use command buttons to save completed sheets and save them to a folder as individual files. These Sheets are orders we run throughout the day, each are renamed automatically by a cell value. I use a command button to add more sheets as more orders come in. Sheets are created by a hidden sheet and named accordingly.
I simply want to move these sheets to the end via VB ... Please Help
(As you view the code you can see that all the new sheets are selected together. All I have to do is send them to the end, but inevitably someone will mess the whole thing up!!!)

Sub ADD4()

On Error GoTo AddSheetsFinalize
Sheets("Sheet (A)").Visible = True
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(1)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (1)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(2)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (2)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(3)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (3)"
Sheets("Sheet (A)").Select
Sheets("Sheet (A)").Copy Befo =Sheets(4)
Sheets("Sheet (A) (2)").Select
Sheets("Sheet (A) (2)").Name = "Sheet (4)"

Sheets("Sheet (A)").Visible = False

Sheets(Array("Sheet (1)", "Sheet (2)", "Sheet (3)", "Sheet (4)")).Select
Sheets("Sheet (1)").Activate

Somewhere here I need the code to send these 4 sheets to the end of the workbook!


End Sub



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