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

Save Workbook With Multiple Sheets As Csv File And Xlsx File

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


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.


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 ...
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 ...
Mark an Excel File as Complete and Final Version
This feature allows you to make an Excel file that tells everyone that it is the final version, the completed vers ...

Helpful Excel Macros

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
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
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
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
Prevent Saving a Workbook under a Different File Name
- This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only

Similar Topics

Thanks in advance for any help

i have a attached a file that will look for a csv file and consolidate all identical text from column A into different worksheets , then it saves to xlsx file.

at present i have to save my xlsx files to csv to run the macro (but unfortunatel all formulas dissapear in the csv format).

i want to be able to search for xlsx files, then let the macro run and save also as xlsx file..

is it possible someone could help me please


After you create a new workbook, you can't close it without Excel asking you where you want to save the file.
However if the workbook has already been saved then Excel will only remind you to save before closing. (It wont ask for a save path)

I want to achieve the former option on a XLSX that has already been saved. i.e. I want the open (already saved) XLSX file to forget where it was saved.

Any ideas?

(Last option I guess would be to copy the workbook to a new workbook then delete the original XLSX)

I have to download files from a database and save them in .xls format (the only format permitted to export from the database)

Anyone have a macro to:

Given an open excel wb saved to my hard drive (or network drive) in non .xlsx format. (most likely .xls)

Switch the file to .xlsx format without loosing the open file. (re-open ok).

-Don't want two files
-Dnn't want to have the file name changed (except for the extension).
-Don't want to remain in compatibility mode.
-Don't want to wait a long time.
-Don't want to have possibility of file corruption.

I just want to take my non .xlsx file and then, turn into a .xlsx file. (if I save the file as .xlsx I am still in compatibility mode until I re-open the file. I also will have two files---very annoying).

Thanks in advance

I have an excel file that is distributed within my organization. That file contains certain worksheets that the end-users do not need to see or make changes to, so in the VBA project, I change those files to "VeryHidden". Then I protect the workbook's VBA code by going to Tools->VBAProject- Project Properties-> Protection tab and adding a password. This worked great as it allowed all the other sheets to function as intended, but didn't allow the users to access the sheets that were hidden.

However, we recently upgraded to Excel 2007, and the password protection is stripped whenever the file is saved with the extension .xlsx. I can save it as a "macro-enabled" file with the extension .xlsm, but all that needs to be done to remove the password is to save it with the .xlsx extension again. Is there something that can be done in Excel 2007 to protect VBA code, or am I doing something completely wrong?

This is my first post here, so please let me know if I've done something wrong!

Hi all,

I have a current workbookCopy of ASP Test RC File.xlsx with 3 worksheets.

Worksheet RC is a summary of all companies rates (Columns G:J). I've shortened this to keep the file small but in reality there are actually another 140 columns after Column G.

What I am looking to do is create an individual file for each Company i.e. each column from G onwards yet keep Columns A:F as these will be the same for all.

Furthermore, I would like each new file contain the Instructions and Cover Page tabs (Sheet 1 & 2).

Each new file should be renamed according to the name listed in G5, G6, G7, etc...

Is there a way to do this with VBA to save me manually having to create 140+ plus files and copying and pasting sheets 1 & 2 into each file?

Any assistance would be greatly appreciated.

Kind regards,

Hi all, I've actually got a proper coding question, for a change.

I'm using a data handling application which exports data to Excel, producing an Excel 2003 worksheet. Unfortunately this means that it gives an error if you try to export more than 256 columns of data (which I do every so often).

I've found a way to make the other appliction split the columns over multiple Excel sheets and I have a macro which merges all of the individual sheets back onto the front sheet.

However, before I can run my macro I need to:

1. Save the exported workbook as an Excel 2007 xlsx file.
2. Close the workbook
3. Re-open the xlsx file.

If I don't do this then my macro fails when the front sheet column count reaches 256.

So, what I'm wondering is:

1. Is there a programmatic way to tell Excel that it's no longer dealing with a 2003 format file and it can safely add columns above 256 or

2. Is there a way to tell if the target workbook is a 2003 file which has just been saved as a 2007 file (in which case my macro can close and re-open it) or if it is a genuine 2007 xlsx file?

Any advice greatly appreciated.


I am trying to merge 30 million rows of records from about 10 different xlsx files into any format that could be imported into ACL (ie: csv delimited text file, tab delimited text file etc). All of these xlsx files already have a consistent set of headers and data type in each of the fields, but a workbook may contain a number of different worksheets.

Is there an easy way to complete this merging exercise with either with a macro or a another program?

I have a report that dumps out an .htm file which I open in excel and run a macro on and then have it prompt to do a save as, with the following chunk of code that I've modified from other posts on this forum:

Dim file_name As Variant

strFileDirectory = "C:\Users\Michael\Documents\"
ChDir (strFileDirectory)
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel workbook (*.xlsx), *.xlsx")
If file_name False Then
ActiveWorkbook.SaveAs Filename:=file_name
End If

It is prompting me correctly enough it would seem to save the file with the correct .xlsx extension, but not only is it saving the .xlsx file but it is creating a folder (of the same name) housing additional files (htm, css, xml). I just want a regular 'ol xlsx file. Any ideas how I can improve the code to get that?

Thanks in advance.

Hi all,

I have the following situation:
I've built a file for other users without macros (or other VBA) and saved as .xlsx
Now I want to protect it from users writing VBA code (to get to the protected cells of the file).
Of course they would have to save the file as .xlsm to be able to use the VBA, but I'd like to prevent them from even getting to the VBA project viewer.

I know I can do this in a .xlsm file with a password, but it doesn't seem to work for my .xlsx file. When I set a password, close the file and open it again, you can get to the (empty) code without Excel asking for the password. Is this because it is not a .xlsm file?

I have an .xlsm file that contains this (part) code below to save as another filename I have attempted to save as another .xlsm file but new file is always corrupted when created.

When I try to save new file as an .xlsx file I am prompted to check compatability - the script stops at "ActiveWorkbook.Save"
- how do I avoid this check appearing?

Sub CreateSummary()
' Sheets("Header").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Sales Summary.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Hi All,

I have code that I am using to copy some sheets from an existing workbook into its own seperate workbook. I have a few problems with it that I was hoping someone could help me clear up. (Forgive me I'm still new to this!) Any help is appreciated. My Problems are as follows:

1. Once the Save As window pops up, the 'Cancel' button doesn't work. The user is forced to save the file.

2. Once the sheets are copied over to the new workbook, the new workbook remains open. I would like it to be saved, but not opened.

3. After the new workbook is created with the copied worksheets, when the user proceeds to close the workbook, it asks the user to save again. I'm guessing the sheets copied to the new file after it was created? I would like the user to not have to do this.

Any help provided is greatly appreciated. Thank you

Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
'Set Default File Path to Save to
ChDir "C:\Documents and Settings\My Documents"
With Application
.DisplayAlerts = False
.ScreenUpdating = False

Set wb = Workbooks.Add

ThisWorkbook.Worksheets(Array("Summary", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8")).Copy befo =Worksheets("Sheet1")
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1").Value = "" Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End If
fName = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xlsx")
Loop Until fName False
ActiveWorkbook.SaveAs Filename:=fName
Next ws
.DisplayAlerts = True
.ScreenUpdating = True
End With
Set wb = Nothing
Set ws = Nothing
End Sub

I get a lot of .xls files from generated reports. I have to go through the Save As dialog every time to save them as .xlsx.

The path is not always the same, nor is the file name. So what I need is a general macro for my background macro workbook that parses the file path, file name, then saves the file as .xlsx with the same name and path. I'm a little stumped on this one! Thanks!

I have received an .csv file and saved this file as an .xlsx file because otherwise my Java program couldn't work with it.
I don't have enough memory to work with the .xlsx file through Java (because the file is 19 MB).
I would like to split the .xlsx file into several .xlsx without having to copy paste all of it.

My Excel file looks like this:




I would like to have a seperate file for each TRAIN_NUM. Is this possible and can somebody tell me how? (I have no experience with macro's.)
Each TRAIN_NUM can have a different amount of rows and the TRAIN_NUMs are not always consecutive.

after running a macro i have a dynamic amount of worksheets within the .xls workbook.

my goal is to save each sheet as its own file with only that sheets within the workbook.

for example if i have 3 tabs (alpha, beta, zeta) on the worksheet....i want to run this code and the end result would be a file saved with only the tabs alpha, a second file save with only one tab called beta and a third file with only zeta.

does that make sense????????......i already have the coding for the fnc of save the file (along with it's cooresponding name) and to the correct location.


I have the below that is taking the numeric response report workseet, exporting it and prompting to save as _ .xls. Having the user fill in the name

How can I add in to reference a different sheet and cell to automatlly fill in the save as box?

Also, does anyone know why if I click cancel it saves the file as false.xlsx?

Sub savefile()
Dim myFile
Sheets("Numeric Response Report").Select
Sheets("Numeric Response Report").Copy
'look into why when canceling save as it automatically save it as False.xlsx

myFile = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel File (*.xls), *.xls")
ActiveWorkbook.SaveAs myFile
MsgBox "File saved as : " & myFile

End Sub

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?

Hi I have this macro it save to specific location but if the file name exist then macro fails or wants to overwrite existing file I will like to make this macro to add a number
So I t will look like this
DISCONNECTED # 11-09-09.xlsx
DISCONNECTED # 11-09-09 2 .xlsx
DISCONNECTED # 11-09-09 3 .xlsx
DISCONNECTED # 11-09-09 4 .xlsx
DISCONNECTED # 11-09-09 5 .xlsx


sub save()       
ActiveWorkbook.SaveAs FileName:= _
"C:\POSTING\  DISCONNECTED   # " & Format(Date, "MM-DD-YY") & ".XLSX", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
end sub

Beforehand thank you for any help that you can provide

I have a workbook with a User Input Form that when saved (via VBA) creates a new file and file name and saves as an xlsx workbook.
How can I save the new file as a PDF.
PS: I need the original file to remain as an Excel Macro Enabled Workbook.

Hi friends,

I daily get a .csv file and i have to open it and daily need to save it as .xlsx so i just want to know is there any macro to save the .csv file with current date .xlsx and to a specific path, so that i just have to open it and click one button and the .csv file will be saved as current date.xlsx please help

hey guys,
i m a beginner in vba. I m having problem in upgrading my excel vba file.i made a stock data entry file in which

1) i want that my file should create another workbook for each location while adding new location name and ignore if it has already created

2) add data to particular location File say USA data to Usa.xlsx Canada data to canada.xlsx ,the location is choose in combo box.

i successfully managed to transfer my data to a another workbook but i need that it should be filtered and saved in different location file name.

File Attached for your better understand my query.

Hi All,

Can anyone help:- I want to be able to save an file created on my computer for use with a device using Windows mobile Ver 6. The files in Windows have the ext .xlsx but I can't find this in the drop down in the save as file type list.

I have files on my mobile device saved as xls, but whenever I change and save them I have to save a copy i.e. Save As.. Filename(1) whereas the xlsx files will just accept the changes and save as they are.

Very grateful for some help with this. Thanks


Hello. I created a design table in excel 2007. I saved it as a .xls file. I want to now take that file and make it xlsx so I can have more than 65k rows. I tried opening the .xls file and did a save as .xlsx but the row limit is still 65k. How do I get it back to handle more than 65,000 rows again? Please advise. Thanks.

I have a set of formulas that are entered vertically. Each one gives a value imported from another file.

For example:

Day 1
A2 - ='[source file.xlsx]Sheet1'!A1
A3 - ='[source file.xlsx]Sheet2'!A1
A4 - ='[source file.xlsx]Sheet3'!A1

So as we move down the "day 1" column the source file remains the same but the sheet within that file changes. I am returning the same coordinate of data (A1) but on different sheets in the source file.

Now i need to set up day 2 which will be in the B column. When i fill over the column it changes the A1 to B1 as expected. Since I am filling horizontally it gives me the horizontal increment change which is column.

What i need is:

Day 2
B2 - ='[source file.xlsx]Sheet1'!A2
B3 - ='[source file.xlsx]Sheet2'!A2
B4 - ='[source file.xlsx]Sheet3'!A2

Day 3
C2 - ='[source file.xlsx]Sheet1'!A3
C3 - ='[source file.xlsx]Sheet2'!A3
C4 - ='[source file.xlsx]Sheet3'!A3

So as i fill over the whole column i need the new row number to be 1 higher. This is because the source file lists data like so:

Day 1 - data
Day 2 - data
Day 3 - data

Hopefully this makes sense! The alternative to this is filling the whole column over, using $ to lock cells and manually adjusting the row number to the next increment for every cell! Huge waste of time

Hi Guys,

I have the following problem. I need a piece of code that would do the following.

I have many excel files that contain links to other spreadsheets and formulas.
I want to be able to make a macro that would do the following:

copy all the sheets from the active "workbook with links" xlsx create a new filek xls replacing name of workbook "workbook with links" xlsx into workbook "workbook" xls. paste as values + formats all the content of "workbook with links" xlsx into "workbook" xls. as I am going to repeat this procedure every time the data changes in "workbook with links" xlsx the macro has to be able to each time overwrite "workbook" xls file.
Thanks for help

I have an .xls file (compatibility mode) containing about 40 worksheets. My goal is to save each worksheet as a separate workbook. To do this, I turned to David Mcritchie's macro, found he

I run this macro in Excel 2007. It works, but I get the compatibility prompt before each save. As you can imagine, this takes forever and basically defeats the time-saving purpose of using the macro to begin with.

In order to get around this, I have experimented with various combinations of which format (xls, xlsx, xlsm) to use for the original file, and also to designate for the ending of the new files, as shown within the VBA code. This includes saving the original file as .xlsm, closing it, opening it up again, and running the macro then, with the macro either in its original form, or modified to save a different type of file for the new workbooks.

But no matter what I do, the macro only gets as far as creating the very first workbook, and then giving an error that I'm trying to paste into a workbook that doesn't have as many rows as the original workbook. Of course, the new workbook is being created by the macro.

I would very much appreciate suggestions, and would be happy to provide more information, or try different solutions. Thanks!