Export sheet to new workbook VBA

VBA Save Sheet as Workbook Excel Macro Code

Home » VBA » VBA Save Sheet as Workbook Excel Macro Code

VBA code to save sheet as Workbook example code will help us to save a specific sheet as new workbook. We can use Copy and SaveAs methods of Workbook to copy and save the sheet into new workbook. In this example we will see how to save Active Sheet and specific worksheet as new excel file using VBA. And this code should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.

VBA code to save Sheet as New Workbook

Here is the Example VBA syntax and Example VBA code to save a Sheet as New Workbook. This will help you to how to save a worksheet as New Workbook using VBA.

VBA Save Sheet as Workbook: Syntax

Following is the VBA Syntax and sample VBA code to Save a Sheet as Workbook using VBA. We are using the Copy and SaveAs methods of the Excel Workbook object.

WORKBOOK1.SHEETS(“WORKSHEET1).COPY BEFORE:= WORKBOOK2.SHEETS(1)
WORKBOOK. SAVEAS “FILE PATH TO SAVE”

Here workbooks can be ActiveWorkbook, ThisWorkbook or a workbook assigned to an object.
ActiveWorkbook. Workbook1 is your source workbook and Worksheet1 is your sheet to copy. And Workbook2 is the destination sheet and sheets(1) and before key words tells Excel to copy the worksheet before the first sheet of workbook2.

Here you can observe that we are copying the worksheet in the first statement. We are using Copy method of workbook to copy the worksheet. Then we are saving the file in as specific location using SaveAs method of Workbook.

Save Worksheet as New Workbook using VBA: Examples

The following VBA code is to Copy the worksheet into new workbook and Save in a specific folder. Sub sb_Copy_Save_Worksheet_As_Workbook() Dim wb As Workbook Set wb = Workbooks.Add ThisWorkbook.Sheets("Sheet1").Copy Before:=wb.Sheets(1) wb.SaveAs "C:temptest1.xlsx" End Sub

Instructions to run the vba code to save a worksheet as new Excel Workbook

Please follow the below steps to execute the vba code to save the worksheet as new excel file.
Step 1: Open any existing Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: Change the code as per your requirement
Step 6: Change the file path as per your testing folder
Step 6: Now press F5 to execute the code

Now you can observe that your worksheet is saved as new Excel workbook in the specified folder.

Explained VBA Code to Save worksheet as new Workbook

‘Starting a procedure to save a worksheet as new workbook
Sub sb_Copy_Save_Worksheet_As_Workbook_C()

‘Declaring a variable as workbook to store the newly creating workbook
Dim wb As Workbook

‘adding a new workbook and seting to wb object
Set wb = Workbooks.Add

‘Copying a worksheet from ThisWorkbook into newly creadted workbook in the above statement
ThisWorkbook.Sheets(“Sheet1”).Copy Before:=wb.Sheets(1)

‘Saving the newly created Excel workbook into required folder with specific workbook name
wb.SaveAs “C:temptest1.xlsx”

‘Ending sub procdure to save a worksheet as new workbook
End Sub

Save Active Sheet as New Workbook using VBA: Examples

The following VBA code is to Copy the active worksheet into new workbook and Save in a specific folder. Sub sb_Copy_Save_ActiveSheet_As_Workbook() Set wb = Workbooks.Add ThisWorkbook.Activate ActiveSheet.Copy Before:=wb.Sheets(1) wb.Activate wb.SaveAs "C:temptest3.xlsx"End Sub

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack
120+ Project Management
Premium Templates

Essential Pack
50+ Project Management
Premium Templates

50+ Excel
Project Management
Templates Pack

50+ PowerPoint
Project Management
Templates Pack

25+ MS Word
Project Management
Templates Pack

Ultimate Project Management Template

Ultimate Resource Management Template

Project Portfolio Management Templates

Share This Story, Choose Your Platform!

38 Comments

The code works, however, I like to do a save as rather than to a particular drive that is written into the code. .Can you show me this change please.
Hi.
Maybe this can help.
Sub sb_Copy_Save_ActiveSheet_As_Workbook(path As String, file As String)
Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy After:=wb.Sheets(wb.Sheets().Count)
wb.Activate
If Right(path, 1) = ” Then path = Left(path, Len(path) – 1)
If MsgBox(“O nome do arquivo está correto? ” & path & ” & file & “.xls”, vbYesNo) = vbYes Then
wb.SaveAs path & ” & file & “.xls”
End If
End Sub
Sub testemain()
Call sb_Copy_Save_ActiveSheet_As_Workbook(“c:”, “teste”)
End Sub
Hi,
Thanks a lot but is it possible to paste it in pastespecial in the destination sheet.
Thank you.
Hi Anil,
We can do as follows:
Sub sb_Copy_Save_ActiveSheet_As_Workbook_PasteSpecial()
Set wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy Before:=wb.Sheets(1)
wb.Activate
wb.SaveAs “C:temptest3.xlsx”
wb.Sheets(1).Cells.Copy
wb.Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
wb.Save
End Sub
Thanks-PNRao!
This is very very helpful. thank you so much for this answer. Just one more litle thing : Can you add a code to delete those extra sheets(except the required sheet) in new workbook ?
Hi Jayant Singh,
Here is the Code:
Sub deletAllSheetExcept() For Each sht In ActiveWorkbook.Worksheets If sht.Name <> "YourSheetName-WhichYouWantToKeep" Then sht.Delete Next End Sub
Thanks-PNRao!
Hi all,
I am very new to VBA, could you please tell me how can I activate the macro to save in different worksheet?
Hi There,

Thanks this has been really useful, how would I add onto the end of the sub to close the file, as I don’t want it to leave the file open.. just saved down into the drive?
Also is it at all possible to include a cell reference in the worksheet in the new file name… eg I want to save the new file as a company name and date the report relates to. The date is a cell field in the worksheet.
Also sorry to be a bother, but I have multiple worksheets in the one file that I am trying to save, however each worksheet is a filtered result but I get an error message saying the copy and paste area is not the same?
Any advice?
Hi Jenna,
Let’s see your second question first:
You can use the file name from a Cell reference: The below code refers the file path from Range B1 of required sheet:
Example Case: If B1 value is C:temptest1.xlsx
Dim strFileName strFileName=Sheets("SheetName").Range("B1") '...... your statements wb.SaveAs strFileName
Your Case: If B1 value is date, B2 value is a company name, And B3 value is target folder
B1=ABCCompany
B2=12/3/2015
B3=”C:temp”
Then the code would be:
Dim strFileName strFileName=Sheets("SheetName").Range("B3") &Sheets("SheetName").Range("B2") &" &Sheets("SheetName").Range("B1") '...... your statements wb.SaveAs strFileName
Now your first question: you can use the Close method of a workbook to close the file
'...... your statements wb.Close ' to close the file
Thanks-PNRao!
Do you want to save all three sheets in one workbook, then you can just use wb.SaveAs ‘YourFilePathandName’
Please describe your issue with more information.
Thanks-PNRao!
Hi Sir
Iam new to VBA coding,i have a problem please help to resolve.
Problem :
I have a multiple sheets and sheet names are based on country name.
I want to create multiple workbooks based on the sheet names in a single run .
Example : i have 5 sheets with different country name like (Ind,Brazil,china,russia,US)
i want to create a separate workbook for India ,separate workbook for Brazil..etc) it should be dynamically pick the sheet name and create a workbook.
Please help to query this issue
I’m trying to have Excel save a file with the contents of a cell (happens to be a date code) but give me the option to edit the file before saving so it would have to display the pop up save as dialog box enter the contents of the specified cell and wait for my further input and to press the save button. Can this be accomplished?
Hi Gene,
Yes, we can read the file path and name from a excel range/cell and use VBA and FileDailog to SaveAS with required name. The below macro will wait for user to press Save button to SaveAs with altered file name at desired file ath:
Sub sbSaveAsExcelDialog() Dim IntialName As String Dim sFileSaveName As Variant InitialName = Range("A1") 'Change the cell address as per your requirement sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _ fileFilter:="Excel Files (*.xls*), *.xls*") ' You can change the file filters as per your requirement If fileSaveName <> False Then ActiveWorkbook.SaveAs sFileSaveName End If End Sub
Please make sure to format the date to accept as a file name (i.e; if you want to use date as a file name, you have to remove the special characters like: /,:,-)
Thanks-PNRao!
Thanks! worked like a charm.
Welcome Gene! I’m glad it worked. Thanks-PNRao
Hallo from me to you all with great skills in programming (my opinion and you can’t change it – sorry!!!)
I did the code above (excel 2010) but when I save it I can’t see a anything anywhere !!!!
What am I doing wrong?
thank you for your time
Private Sub cbSaveAs_Click()
Dim IntialName As String
Dim sFileSaveName As Variant
InitialName = Range(“B6”).Value & “-” & Range(“B9″).Value ‘it takes the B6-B9 name – its the only code I changed Change the cell address as per your requirement
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:=”Excel Files (*.xls*), *.xls*”) ‘ You can change the file filters as per your requirement
If fileSaveName False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub
What I want to do is this:
Working in workbook with userform with buttons
So it creates a new sheet with B6 & B9 cell values
Afterwards with this macro I want to create a new Excel file with the name of B6 cell and transfer in there the sheet with B6 & B9 cells value.
Just that most of the times the B6 value maybe the same for example:
sheet like this 8620-112233 and sheet with 8620-998877 these 2 sheets must be stored in a file 8620.xlsx.
The only thing is that today I create the 1rst sheet and 1 week later I create the 2nd sheet. So I must transfer the 2nd sheet INTO the old file (8620.xlsx) but keep the 1rst sheet in that file and I have to do with a button to be simple.
Else we go to the 1rst solution as above but with the problem I mentioned
Sorry for making 3 posts
HI,
You posted this code back in August but I am now looking at this in Oct. I have situation where I need to tweak the following cole with something like this below. How would you tweak the August code with the one that I’m working on now:
My project is to create a separate sheet from a pivot table where I have the filter on Doctor (I make a sheet for each doctor using the Pivot Table).
I need to move this sheet from the current workbook to a new file – with the doctors name and billing period The file needs to be named something like “[Doctor’s Name from the sheet from the Pivot Table] – Q3 Billing”. I would also like to make this flexible enough to change the Q3 to another period in the future. this file is going to be reused on a monthly basis so I would like to set it up so it is flexible to accommodate the File name – all at one time (I may have 10-15 doctors in one pivot table for a particular month).
The code I’m working with now:
Sub CopySheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name “Master” Then
ws.Copy
With ActiveSheet.Cells
.Copy
.PasteSpecial xlPasteValues
End With
With ActiveWorkbook
.SaveAs “Drive:Filepath” & ActiveSheet.Range(“A1”).Value & “.Xlsx”, FileFormat:=51
.Close
End With
End If
Next ws
End Sub
The August code you added
Sub sbSaveAsExcelDialog()
Dim IntialName As String
Dim sFileSaveName As Variant
InitialName = Range(“A1″) ‘Change the cell address as per your requirement
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:=”Excel Files (*.xls*), *.xls*”) ‘ You can change the file filters as per your requirement
If fileSaveName False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub
Going back to August 14, you posted the following code:
Sub sbSaveAsExcelDialog()
Dim IntialName As String
Dim sFileSaveName As Variant
InitialName = Range(“A1″) ‘Change the cell address as per your requirement
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:=”Excel Files (*.xls*), *.xls*”) ‘ You can change the file filters as per your requirement
If fileSaveName False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub
I’m working on the following code (where I am making a sheet based upon a pivot table then moving it to a new file with the
Doctor’s name). How do I incorporate this piece (sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:=”Excel Files (*.xls*), *.xls*”) ‘ You can change the file filters as per your requirementinto the follwing code”
Sub CopySheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name “Master” Then
ws.Copy
With ActiveSheet.Cells
.Copy
.PasteSpecial xlPasteValues
End With
With ActiveWorkbook
.SaveAs “Drive:Filepath” & ActiveSheet.Range(“A1”).Value & “.Xlsx”, FileFormat:=51
.Close
End With
End If
Next ws
End Sub
Thank you for your help.
Hello
I am new to this part of excel and was wondering if you could help me.
Could you show me the code I would need to do the following.
To save the current WORKBOOK
To create and save a new workbook with all the sheets copied (Time sheet, Pay sheet, Data, Pay Data, Tax)
The new workbook name being 14 days after either the current workbook name or “Time sheet”H4
I would then like the data in the new workbook Time sheet cells C8 to C14, D8 to D14, C17 to C23 and D17 to D23 erased or deleted.
I would also like the selected option in cells B8 to B14 and B17 to B23 to come up blank(waiting for a selection from the list) or to have the “DAY OFF” selection appear.
I would like the valve from the current workbook “Time sheet”G28 and “Time sheet”G29 to be linked to the new workbook “Time sheet”C28 and “Time sheet”C29
Could you help with this. Cheers Steve
Hi There!
How do I copy select columns from multiple tabs to a work sheet (same columns in different tabs by month).
At 72 years old, I decided to start playing with VBA. I have managed to get a few ‘programs’ working in my XL2007 and need some help with the following. I have an invoicing program that I wrote with the help of information from the net and what I got stuck with now is that I would like to print the resulting Invoice on a PDF file for sending to customer. Considering that I have 8 working sheets on the file and need to only print the one sheet (Invoice). Can you help me overcome this by sending me a code to type in. I will modify file names etc as well as the directory as each customer has he one directory. So the PDF saved will have the customer’s name & Invoice Number & Date. I will be most grateful with any help I can get. Thank you and keep up the great work you are doing. Knowledge must be spread and not limited or controlled by the few.
Peter Theodorou
Hello Every one,
Greetings!
Please help me on one thing,
I want to save current worksheet of open workbook
as new worksheet in the same workbook.
For example if SheetA is current w.s. then save this sheet
in the same w.b. with name CopySheetA
and save this sheet on every 1 or 2 second.
thanks & rgds
I have a workbook with 10 sheets I only want to save 3 of the 10 sheets into a file how can i do this?
Hi.
I wonder if you can help. I am trying to save a workbook with the data from one of the cells in one of the active worksheets. I have renamed all the sheet no. so I don’t know if this has an effect.
For example I have 5 active sheets within the workbook let call them RED ONE, GREEN ONE, BLUE ONE, ORANGE ONE, YELLOW ONE.
Im trying to save the entire workbook into a destination folder using the save name from cell C7 on sheet ‘RED ONE’.
Heres what I have so far.
Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveWorkbook.Sheets.Copy
NewFN = “\C:MCM CloudEstimatingQuote NumbersQUOTE_” & Range(“C7”).Value & “.xlsx”
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NEXTINVOICE
End Sub
This works apart from it doesnt save new name from cell ‘C7’ on worksheet ‘RED ONE’
Can you help.
Regards
Matt
Hi.
I wonder if you can help. I am trying to save a workbook with the data from one of the cells in one of the active worksheets. I have renamed all the sheet no. so I don’t know if this has an effect.
For example I have 5 active sheets within the workbook let call them RED ONE, GREEN ONE, BLUE ONE, ORANGE ONE, YELLOW ONE.
Im trying to save the entire workbook into a destination folder using the save name from cell C7 on sheet ‘RED ONE’.
Heres what I have so far.
Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveWorkbook.Sheets.Copy
NewFN = “\C:MCM CloudEstimatingQuote NumbersQUOTE_” & Range(“C7”).Value & “.xlsx”
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NEXTINVOICE
End Sub
This works apart from it doesnt save new name from cell ‘C7’ on worksheet ‘RED ONE’
Can you help.
Regards
Matt
Hi Ankit,
I have a list of production Csv files, out of which the month column “B” needs to be changed every month for all the list of files, for which i have created a macro, which amends only colum “B” which is 85 % working files for all the files, except few where apart Column B , even colum “E ” (hold some value) .
Issue is because , the files are Csv files, if i amend the macro with .xls, it’s 100% working fine. I even tried by converting the Csv files to .xls but still however i could find the value changed files
Is there any way, i can make sure that except “B” colum , no other column gets changed
Dear Sir(s)
I have a situation where we sell different varieties of items at different prices to regular customers. the prices vary on a day to day basis and so does the quantity that each customer buys.
My requirements are that when we input the quantity and rates, we get the amount. i have made 31 work sheets in my masterfile for each date and has the list of all my customers with provision to insert their purchase and the price. Now I want a weekly total of the sales that we have made. I have linked the autosum to a differnt file named weekly report. This i would like to consolidate for monthly and then yearly basis. The problem is that the 31 sheets in my daily log book are finished and so is my weekly data completed for a month. Now i want to know how to write a code so that at the end of each month my daily log book and weekly report is saved by the name of the month at the end automatically and the process of updating each individual month then starts from the master file. Also once the file is saved with a new name will i lose my data that is still linked to the file? Kindly help me please.
Thanks in advance
Hi All,
I would like to assign a macro to a button which when pressed will copy and paste special a sheet into a new workbook. This workbook should get a name which is in a cell C8 of this active sheet. Also I would like to save this file into a folder name which will be in cell C9. Of course there will be a fixed path to this folder. If folder does not exist yet I would like this to be created.
Can you help me with this?
Thanks in advance
Marcin
Hey,
I want to export one worksheet as a copy of the data that will just open when button (Marco) is pushed. Then I can save where ever i want and whoever uses the workbook can use the same.
Regards
dear sir
how to write this plz help me regards given below
Sub saveinvwithnewname()
Dim newfn As Variant
‘copy invoice to a new workbook
ActiveSheet.Copy
newfn = “c:ssinv” & Range(“i5”).Value & “.xlxs”
ActiveWorkbook.SaveAs newfn, FileFormat:=XlopenXMLWorkbook
ActiveWorkbook.Close
nextinvoice
End Sub
How code copy two sheet to new workbook?
Sub sbCopySheetsToNewWorkbook() Sheets(Array("Sheet2", "Sheet3")).Copy End Sub Sub sbCopySheetsToSpecificWorkbook() Sheets(Array("Sheet2", "Sheet3")).Copy Before:=Workbooks("Book3").Sheets(1) End Sub
Hello,
Thank you very much for all of the above, it has been really useful for me.
I have one issue, i need to save the new file as a csv file, how would i do this (forgive me i am a VB newbie)
So far i have;
Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim strFileName
strFileName = Sheets(“JobCard”).Range(“E3″) & ” & Sheets(“JobCard”).Range(“E5”) & “.” & csv & ”
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets(“csv”).Copy Before:=wb.Sheets(1)
wb.SaveAs strFileName
End Sub

Thanks,
April
Sorry this is the code i have;
Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim strFileName
strFileName = Sheets(“JobCard”).Range(“E3″) & ” & Sheets(“JobCard”).Range(“E5”)
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets(“csv”).Copy Before:=wb.Sheets(1)
wb.SaveAs strFileName
End Sub
There are multiple sheets from sheet1 to sheet13 in one workbook abc.xlsm (macro language file).The query is need to transfer sheets from sheet 1 to sheet13 into different workbook as sheet1 from abc.xlsm will transfer into r1.xls, sheet2 from abc.xlsm will transfer into r2.xls.

Effectively Manage Your
Projects and Resources

With Our Professional and Premium Project Management Templates!

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Project Management
Free Templates
Premium Templates
Project Plan
Project Activity
Project Appraisal
Project Budget
Project Charter
Project Resource
Project Status
Project Timeline
Gantt Chart

Excel VBA

Blog
Excel Tutorial
VBA Tutorial
VBA Code Explorer
15 Excel Macros for Beginners
100+ Most Useful VBA Macros
VBA Projects
Templates
Dashboards
Downloads
Knowledge Base

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Page load link

Go to Top

How do you copy a sheet to a new workbook in Excel VBA?

How to duplicate sheets in Excel with VBA.

Open the worksheet you want to copy..

Press Alt + F11 to open the Visual Basic Editor..

On the left pane, right-click ThisWorkbook, and then click Insert > Module..

Paste the code in the Code window..

Press F5 to run the macro..

How do I save an Excel worksheet to another workbook?

Save a worksheet.

Right-click the worksheet name tab..

Click select Move or Copy..

Click on the Move selected sheets to Book drop-down menu. Select (new book)..

Click OK. Your new workbook opens with your moved worksheet. ... .

Click File > Save in your new workbook..

How do you extract data from Excel to another Excel File using VBA?

To extract data from another workbook using a Macro, you have to provide the workbook name and full path to a procedure (or a piece of code) to process. You can hard code the file name and path, store it in a variable, and then process it.