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.
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.