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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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:\temp\test1.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:\temp\test1.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:\temp\test3.xlsx"End Sub 
LIMITED TIME OFFER
By |April 22nd, 2014|VBA|33 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

33 Comments

  1. JOE CUCE October 1, 2014 at 2:50 PM - Reply

    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.

    • renato lacerda October 28, 2014 at 1:42 AM - Reply

      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

  2. Anil Kumar November 3, 2014 at 3:24 PM - Reply

    Hi,

    Thanks a lot but is it possible to paste it in pastespecial in the destination sheet.

    Thank you.

    • PNRao November 3, 2014 at 8:20 PM - Reply

      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:\temp\test3.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!

    • matthew r September 9, 2016 at 2:45 PM - Reply

      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 Cloud\Estimating\Quote Numbers\QUOTE_” & 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

  3. Jayant SIngh January 29, 2015 at 1:14 PM - Reply

    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 ?

    • PNRao February 3, 2015 at 10:08 PM - Reply

      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!

  4. Adina March 11, 2015 at 12:24 AM - Reply

    Hi all,

    I am very new to VBA, could you please tell me how can I activate the macro to save in different worksheet?

  5. Jenna April 13, 2015 at 8:24 AM - Reply

    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.

    • PNRao April 13, 2015 at 8:52 PM - Reply

      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:\temp\test1.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!

  6. Jenna April 13, 2015 at 8:37 AM - Reply

    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?

    • PNRao April 13, 2015 at 8:56 PM - Reply

      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!

      • TruVET July 6, 2016 at 4:39 PM - Reply

        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?

  7. Feroz June 11, 2015 at 6:28 PM - Reply

    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

  8. Gene August 15, 2015 at 6:09 PM - Reply

    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?

    • PNRao August 16, 2015 at 2:24 AM - Reply

      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!

      • Michael D October 12, 2015 at 6:51 AM - Reply

        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.

  9. Gene August 18, 2015 at 9:40 AM - Reply

    Thanks! worked like a charm.

    • PNRao August 18, 2015 at 12:50 PM - Reply

      Welcome Gene! I’m glad it worked. Thanks-PNRao

  10. Costas Pap September 28, 2015 at 3:06 AM - Reply

    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

  11. Costas Pap September 28, 2015 at 4:13 AM - Reply

    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.

  12. Costas Pap September 28, 2015 at 4:24 AM - Reply

    Else we go to the 1rst solution as above but with the problem I mentioned
    Sorry for making 3 posts

  13. Michael October 11, 2015 at 10:55 PM - Reply

    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

  14. Steve October 30, 2015 at 8:12 AM - Reply

    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

  15. char January 13, 2016 at 5:45 AM - Reply

    Hi There!

    How do I copy select columns from multiple tabs to a work sheet (same columns in different tabs by month).

  16. Peter Theodorou April 16, 2016 at 5:08 PM - Reply

    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

  17. Mehul June 2, 2016 at 6:24 PM - Reply

    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

  18. matthew r September 9, 2016 at 2:46 PM - Reply

    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 Cloud\Estimating\Quote Numbers\QUOTE_” & 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

  19. Rina January 18, 2017 at 12:54 PM - Reply

    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

  20. Tanzeel February 10, 2017 at 1:59 AM - Reply

    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

  21. Marcin February 10, 2017 at 7:48 PM - Reply

    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

  22. Jarrod Sandri March 13, 2017 at 9:57 AM - Reply

    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

  23. sufiyan March 21, 2017 at 12:47 PM - Reply

    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:\ss\inv” & Range(“i5”).Value & “.xlxs”
    ActiveWorkbook.SaveAs newfn, FileFormat:=XlopenXMLWorkbook
    ActiveWorkbook.Close
    nextinvoice
    End Sub

Leave A Comment


Related pages


vba using excel functionshow to find duplicate entries in excel 2007vba programming guideexcel cell reference vbaexcel to xml conversionword activex controlswriting vba codeexcel vba blank cellcommon excel commandsadding columns in excelexcel vba sheets.addvba object oriented programmingscripting.filesystemobject delete fileprotecting worksheets in excelunlock excel macrousing the vlookup functionvba create new workbookexcel functions vbainsert worksheetfind duplicates excel 2003horizontal lookup in excelexcel advanced filter vbalogical functions in excel 2007interview questions and answers in c programming language pdfsql important interview questionsvba popup messagems access vba create tablehow to protect a column in excel 2007project planning spreadsheetarray function in vbacheckbox tutorialtask scheduler excel templateexcel sumif examplecreating a macro in excel 2007how to use the choose function in excelvba byvalpivot table vba excelestimate worksheet templatedelete columns vbac language interview questions and answers pdfunlock password protected excel workbookwhat is ddl and dmlexcel remove rows containingmacro code in excelnested if functions in excel 2013microsoft excel 2010 test questions and answersexcel sumif functioncomplex pivot tablesexcel 2013 remove password protectionunlock excel macrocall vbaadodb.recordset exampleexcel add developer tabdeleting duplicates excelunprotect a workbook without passwordsave xls as xmladd item combobox vbavba sort listboxcombine multiple excel worksheets into oneexcel vba coding tutorialvba to open workbookdml examplesmacro tutorial excelsteps to create dashboard in excel 2007vba excel case statementmultiple if statements in excel 2010do while loop vbaexcel calculate weeks between two datesvba excel forms for data entryworksheet.protectvba excel sortexcel logic formulashow to adjust all column width in excelgoto vbawhat is meant by vlookup in excel