How to Save Worksheet as New File Using Excel VBA in 3 Minutes

Written by Kasper Langmann

Excel VBA, or Visual Basic for Applications, is a powerful tool that can significantly enhance your productivity in Excel. One of the most common tasks you might need to perform is saving a worksheet as a new file. This article will guide you through the process in just three minutes.

Understanding Excel VBA

Excel VBA is a programming language developed by Microsoft. It is primarily used for automating tasks in Excel. With Excel VBA, you can create macros, user-defined functions, and manage data analysis among other tasks. It’s a powerful tool that can save you a lot of time if you know how to use it properly.

One of the most common tasks you might need to perform with VBA is saving a worksheet as a new file. This can be useful in a variety of scenarios, such as when you need to send a specific worksheet to a colleague, or when you want to save a backup of a worksheet before making changes to it.

The Basics of VBA Programming

Before you can start using VBA to save worksheets as new files, you need to understand the basics of VBA programming. VBA is a relatively easy language to learn, especially if you’re already familiar with Excel. It uses a straightforward syntax and has a lot of built-in functions that make it easy to manipulate Excel data.

The first step in using VBA is to open the VBA editor. You can do this by pressing Alt + F11 on your keyboard. This will open a new window where you can write and manage your VBA code.

Understanding the VBA Code Structure

Every VBA program is made up of procedures, which are blocks of code that perform a specific task. There are two types of procedures in VBA: Sub procedures and Function procedures. Sub procedures are used to perform actions, while Function procedures are used to calculate and return values.

To save a worksheet as a new file, you’ll need to create a Sub procedure. This procedure will contain the code that tells Excel what to do. The basic structure of a Sub procedure looks like this:

Sub ProcedureName()
' Code goes here
End Sub

Once you’ve created your procedure, you can run it by pressing F5 on your keyboard while the procedure is selected.

Creating a VBA Procedure to Save a Worksheet as a New File

Now that you understand the basics of VBA programming, you’re ready to create a procedure that will save a worksheet as a new file. This procedure will use the ‘SaveAs’ method, which is a built-in Excel function that allows you to save a file in a specific location with a specific name.

The ‘SaveAs’ method has several parameters, but the two most important ones are ‘Filename’ and ‘FileFormat’. The ‘Filename’ parameter specifies the name and location of the new file, while the ‘FileFormat’ parameter specifies the format of the new file (for example, .xlsx or .csv).

Writing the VBA Code

The first step in creating your procedure is to open the VBA editor by pressing Alt + F11 on your keyboard. Once the editor is open, you can create a new procedure by typing the following code:

Sub SaveWorksheetAsNewFile()
' Code will go here
End Sub

Next, you’ll need to add the code that will save the worksheet as a new file. This code will go inside the procedure you just created. Here’s what the code looks like:

Sub SaveWorksheetAsNewFile()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    With ActiveWorkbook
        .SaveAs "C:tempNewFile.xlsx", FileFormat:=51
        .Close SaveChanges:=False
    End With
End Sub

This code first defines a worksheet object ‘ws’ and sets it to the worksheet you want to save as a new file. It then copies the worksheet and saves it as a new file in the specified location. The ‘FileFormat:=51’ specifies that the new file should be saved as an .xlsx file.

Running the VBA Code

Once you’ve written your code, you can run it by pressing F5 on your keyboard while the procedure is selected. This will save the specified worksheet as a new file in the specified location.

Keep in mind that you’ll need to replace “Sheet1” with the name of the worksheet you want to save, and “C:tempNewFile.xlsx” with the location and name you want for your new file.

Conclusion

Excel VBA is a powerful tool that can save you a lot of time and effort. By understanding how to use it to save worksheets as new files, you can automate one of the most common tasks in Excel and increase your productivity. Remember to always test your code on a copy of your data to ensure it works as expected.

With just a few lines of code, you can create a procedure that saves a worksheet as a new file in just three minutes. So why not give it a try? You might be surprised at how much time you can save.