Let’s say you’re working on automating a workbook and after formatting and manipulating the data, you want to save a specific worksheet to it’s own workbook by itself somewhere for use in some other application or to share.

You can easily do that.

This block of code can do a few different things, but I will run through the different scenarios and show you what you can do, why you would do it, and what the differences in code means.


Scenario 1: Saving a Worksheet in One Place

This first block of code simply takes the active worksheet and saves it in a location you specify.


Note: When using this block of code, the file being saved will overwrite a file with the same filename in the same save location. So if you’re using this daily to refresh data, there will only be one file in the folder, and it will be written each time this process runs. So keep that in mind as you build your file. There is an option to archive data, and we’ll go over that in Scenario 2.


If you plan to use this as a larger process and want to use VBA to navigate to a specific sheet to ensure you are on the right worksheet, you can insert the below line of code to make sure your active worksheet is the one that is saved.

'Makes "Purchase_Orders" the active worksheet
Sheets("Purchase_Orders").Select

Next, the full block of code

Sub SaveNewWorksheet()

'Save the active sheet as an independent workbook 
'in the new file path
'Written by JamesCanCode
'www.jamescancode.com

'Declare Variables
Dim SaveWB As Workbook
Dim NewFilePath As String
Set Sourcewb = ActiveWorkbook

'Set the location for the new file
'You would change between the quotes to your file path
NewFilePath = "C:\SaveData\Purchase_Orders.xlsx" 

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set SaveWB = ActiveWorkbook
With SaveWB

    'Save the new workbook and close it
    ActiveWorkbook.SaveAs Filename:=NewFilePath, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close


End with

End Sub

That’s it. That’s the simple version of the code just saving one worksheet in one place.


Scenario 2: Saving a Worksheet in Two Places, One being an Archive

Next we will look at a block of code that’s a bit more in depth – this one saves the sheet in two locations, with the second location being an archive folder that also renames the file to the current date/time so files won’t be over-written as they are archived.

The archive process is useful if you plan to build this as part of a larger project and want to keep snapshots of the data according to the date and time they were downloaded.

Sub SaveNewWorksheetWithArchive()

'Save the active sheet as an independent workbook 
'in the new file path, then save another copy
'with the date and time in the filename to an
'archive folder
'Written by JamesCanCode
'www.jamescancode.com

'Declare Variables
Dim SaveWB As Workbook
Dim NewFilePath As String
Dim ArchiveFilePath as String
Set Sourcewb = ActiveWorkbook

'Set the location for the new file
'You would change between the quotes to your file path
NewFilePath = "C:\SaveData\Purchase_Orders.xlsx"
ArchiveFilePath = "C:\SaveData\Archive\Purchase_Orders " _
     & Format(Now, "mm-dd-yyyy hh-mm-ss") & ".xlsx"

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set SaveWB = ActiveWorkbook
With SaveWB

    'Save the new workbook and close it
    'Start with saving the source workbook
    ActiveWorkbook.SaveAs Filename:=NewFilePath, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    'Then save the archive workbook
    ActiveWorkbook.SaveAs Filename:=ArchiveFilePath, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close

End With

End Sub

An Alert to Plan For

After you have run this once already, when you run it again, you will get an alert pop up:

This is perfectly normal, Excel just wants to make sure that you really want to over-write a file that already exists with the same name. More than likely, you do.

The below line of code inserted at the top of the above sub procedures will suppress this alert:

'Turn Alerts Off
Application.DisplayAlerts = False

But when your procedure is wrapping up, you will want to turn the alerts back on by inserting this line of code before the sub procedure ends:

'Turn Alerts On
Application.DisplayAlerts = True

I generally don’t like to include these sorts of controls in procedures when I’m teaching before I explain what they do. I want users to see the alerts and understand what they are before I explain how to eliminate them.

For example, this is an important control because if you fail to turn the alerts back on, when you resume normal operation of your worksheet – Excel may not be able to alert you to something that’s important, that you really needed to see.


Before You Go…

There’s just one last thing I want to include in this tutorial that may be a little intimidating to understand, but I will try my best to explain it thoroughly so anyone can understand.

I want to explain how to set variables to refer to a specific cell in a worksheet, and also explain how to nest variables inside of one another. This doesn’t only pertain to JUST to this tutorial, you can use it anywhere – but learning this may help you with setting up the filename variables in this tutorial if you plan to use this for a big project.

When we set our file path variables above, they looked like this:

NewFilePath = "C:\SaveData\Purchase_Orders\Purchase_Orders.xlsx"

But what happens if we need to change that file path? Maybe the folder name needs to be changed, or we need to change the root of the file path to something different other than “SaveData”?

If we placed this line of code in just one sub procedure – then it’s not that big of a deal. We can go into the editor, find the sub procedure, navigate to that line of code and just change it to what the new file path should be.

But what happens if we used it in multiple places? Or say, 50? Do you really want to have to go into each module, search for the file path you need changed, and change it? Even using Control+F to replace it can be a real pain.

Worse case scenario…what if someone is maintaining the file who isn’t familiar with the Visual Basic Editor, or VBA just in general? Asking them to change the file path could be a serious project, or just not possible for them to do.


It’s because of this that I have gotten into the habit of setting some variables to refer to locations in a worksheet – this makes it super simple to update those references should something change later on down the line.

The perfect example of this is the VBA Code to log in to SAP that is available right now in another blog post on my site. The Username and Password are two variables needed to login to SAP.

But passwords change…

If I have this login procedure in 40 or 50 different procedures, across 10 modules, every time my password changes, I would need to go into each module and update the passwords.

No thanks…

It would be a lot easier if I only had my password in ONE place, an easy place for anyone who is maintaining the system to change it, and just update it there.

So that’s what we’re going to do, and I will show you how to do it.


For the example in this tutorial, I setup some folders to save our data in.

Simple C drive folder named “SaveData”, with just three folders “Contract_Data”, “Purchase_Orders” and lastly “Vendor_Data”.

I setup a worksheet in my workbook, named it “FilePaths” and set it up to mimic how I setup my folders. In cell B2 I have the root path “SaveData”, then the three folders below that. In Column C I setup the file names I want each file to have.

Now, when I go to setup my variables in the editor, I will set them up like this:

'Declare Variables
Dim RootPath as String
Dim ContractFilePath as String
Dim NewFileName as String
Dim NewFilePath as String

'Set Variables
RootPath = Sheets("FilePaths").Range("B2")
ContractFilePath = Sheets("FilePaths").Range("B3")
NewFileName = Sheets("FilePaths").Range("C3")
NewFilePath = RootPath & ContractFilePath & NewFileName & ".xlsx"

The last line is the variable that the VBA code will ultimately use, and this is a combination of the variables written above it putting them all together.

Now, when I refer to “NewFilePath” in the VBA sub procedures that save the worksheet, the result will be “C:\SaveData\Contract_Data\ContractData.xlsx”

If something changes down the road, all I need to do is go to the “FilePaths” worksheet in my workbook, and just make one change, then all procedures using any of those references will automatically reflect that change.

Advertisements