Email:      Pass:    Pass?
Close Window   
Subscribe for Email Updates!
Excel tips, help, and 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.

Similar Excel Video Tutorials

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
Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun

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.



Hi all,

Is there any way in excel 2007 to define a standard header and footer on the first sheet and use the same header and footer for all the remaining sheets within the workbook (same font and style as in first page).

Since I have around 25 sheets within the workbook i cannot do formatting and copy paste all the time. This will save my time alot.

Thanks in advance...


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.


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

S Bárbara

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 all,

I am using a macro posted here by Ron de Bruin (THANKS!) that separates
worksheets into separate files, but I am having a minor issue with it... the
macro is as follows:

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
Set wb = ActiveWorkbook
wb.SaveAs "C:\Separate\" & wb.Sheets(1).Name & ".xls"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub

The macro works fine, but when I try to open the output files, I receive a
"File already in Use" violation. I can open it in read only and then save it
as something else, but wondering if there is something that can be added to
the macro to fix this condition?


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!

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 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 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?


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 have a big workbook. Since it takes so long to save a workbook when I only change 1-2 cells. If I split it up and link say, my input page (book1) to a huge database (book2), will have any errors later. I do have to forward this to several people now as a zip file. But if I separate and link to each other, I can send as individual files which they would like, but since they store them on their own pc....what would be any issues????

I guess my biggest fear is loosing the link from book to book2 during this process?

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