EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Tips VBA Save Workbook
Secondary Sidebar
VBA Tips
  • VBA Tips
    • VBA RGB
    • VBA Web Scraping
    • VBA AutoFill
    • VBA GoTo
    • VBA Color Index
    • VBA Wait
    • VBA Paste
    • VBA Copy Paste
    • VBA Remove Duplicates
    • VBA Sleep
    • VBA Font Color
    • VBA PowerPoint
    • VBA Borders
    • VBA Pivot Table
    • VBA Save As
    • VBA Tutorial For Beginners
    • VBA Charts
    • VBA Dictionary
    • VBA Conditional Formatting
    • VBA Paste Values
    • VBA Pause
    • VBA Refresh Pivot Table
    • VBA Macros
    • VBA Examples
    • Programming in Excel
    • VBA SendKeys
    • VBA Save Workbook
    • VBA PasteSpecial
    • VBA Function in Excel
    • VBA Visual Basic Applications
    • VBA Return

VBA Save Workbook

By Ashwani JaiswalAshwani Jaiswal

VBA Save Workbook

Excel VBA Save Workbook

In this article, we will see an outline on Excel VBA Save Workbook. It is very easy to create a macro for saving a Microsoft file. The code ranges from 1 line to 10 lines of code. And it is all up to us and our application which code we need to use. Although, a file can be saved just by pressing the shortcut key Ctrl+S. Or else click over the Save Icon which we can easily get at the top of the Excel file. That location is called the Title bar. And this is a location at the extreme left top corner on it. When the process of saving a file is so easy then why we need an extra step to make it happen. Reason can be different for different people, but major justification is when a huge data set in which we want to automate that fully or partially, saving the file after everything is done, may freeze the file. So it is better to save the file once the complete code is run, the process is done. By this, we can save extra time saving the huge data file in additional process and avoid file getting hung or crash. We will be seeing all the different types of code in upcoming examples.

How to Save Workbook in Excel VBA?

The following examples will teach us how to Save the Workbook in Excel by using the VBA Code.

You can download this VBA Save Workbook Excel Template here – VBA Save Workbook Excel Template

VBA Save Workbook – Example #1

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Step 2: Now write the subprocedure for the VBA Save workbook or we can choose any name to define it.

Code:

Sub VBA_SaveWorkBook()

End Sub

VBA Save Workbook Example1-1

Step 3: Now to select the current workbook, use Active workbook as shown below followed by a dot.

Code:

Sub VBA_SaveWorkBook()

ActiveWorkbook.

End Sub

Active workbook Example1-2

Step 4: Search Save function from the list.

Code:

Sub VBA_SaveWorkBook()

ActiveWorkbook.Save

End Sub

Save function Example1-3

Now run the code by clicking on the Play button located below the menu bar.

Just to see whether the file is getting saved or not, keep VBA and Excel window parallel to each other. We will see, the file is now saved by showing the quick process of process.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,641 ratings)

VBA Save Workbook – Example #2

There is another way to save a workbook with one line of code. For this, we can use the same code which we have seen in example-1. For this, follow the steps below:

Step 1: Now in the same code, we will replace Active Workbook with ThisWorkBook. This is another way to select the current workbook which is selected. Now again run the code. We will see the file is save just by showing the waiting blue circle in quick seconds.

Code:

Sub VBA_SaveWorkBook2()

ThisWorkbook.Save

End Sub

VBA Save Workbook Example2-2

Now moving to a little complex set of code but still also an easy way to save any workbook. For this, follow the steps below:

Step 2: Write the subprocedure for VBA Save Book as shown below.

Code:

Sub VBA_SaveWorkBook3()

End Sub

VBA Save Workbook Example3-1

Step 3: In this example, we will be using the For Each-Next loop to perform saving workbooks. Now define a variable using DIM as type Workbook.

Code:

Sub VBA_SaveWorkBook3()

Dim Workbook As Workbook

End Sub

DIM Example3-2

Step 4: Now we will open a For loop write the condition for each defined variable Workbook activate the applications in the workbook.

Code:

Sub VBA_SaveWorkBook3()

Dim Workbook As Workbook
For Each Workbook In Application.Workbooks

End Sub

For loop Example3-3

Step 5: Now use Workbook along with function Save as shown below.

Code:

Sub VBA_SaveWorkBook3()

Dim Workbook As Workbook
For Each Workbook In Application.Workbooks
Workbook.Save

End Sub

VBA Save Workbook Example3-4

Step 6: Close the loop by Next and using defined variable Workbook here as well.

Code:

Sub VBA_SaveWorkBook3()

Dim Workbook As Workbook
For Each Workbook In Application.Workbooks
Workbook.Save
Next Workbook

End Sub

Loop by Next Example3-5

We can compile the complete code and run. We will notice again, quickly the code has saved the file again.

VBA Save Workbook – Example #3

There is another easy way to save the workbook using VBA code. For this, follow the below steps:

Step 1: Write the subprocedure for VBA Save Workbook.

Code:

Sub VBA_SaveWorkBook4()

End Sub

VBA Save Workbook Example4-1

Step 2: Here also, we will need a variable which we used in the previous example.

Code:

Sub VBA_SaveWorkBook4()

Dim Workbook As Workbook

End Sub

VBA Save Workbook Example4-2

Step 3: Now use Set object along with a defined variable.

Code:

Sub VBA_SaveWorkBook4()

Dim Workbook As Workbook
Set Workbook =

End Sub

VBA Save Workbook Example4-3

Step 4: Then use Workbooks function and in brackets insert the name of the workbook which we want to save. Here the name of the workbook is “VBA Save Workbook” with extension. This should be the name of the file in which we are writing the code.

Code:

Sub VBA_SaveWorkBook4()

Dim Workbook As Workbook
Set Workbook = Workbooks("VBA Save Workbook.xlsm")

End Sub

VBA Save Workbook Example4-4

Step 5: Now use a Workbook variable with Save.

Code:

Sub VBA_SaveWorkBook4()

Dim Workbook As Workbook
Set Workbook = Workbooks("VBA Save Workbook.xlsm")
Workbook.Save

End Sub

VBA Save Workbook Example4-5

Once done, run the code. We will notice the small waiting circle will appear for a few moments and the file is got saved.

Pros & Cons of VBA Save Workbook

  • It is the easiest code in VBA to write.
  • All the examples shown in the article are easy to implement.
  • Not useful if used in a small set of code and for small data sets.

Things to Remember

  • It is advised to use VBA Save Workbook code, once we complete the rest of the line of code or at the end of the entire code line.
  • VBA Save Workbook is not limited to the examples which we have seen in the above article.
  • Quote the name of the file in inverted commas always.
  • Use macro enable excel format extension. It is the only version of MS Excel where we can save our VBA Code.
  • Not advised to use this VBA Save Workbook in a small code structure. It will not give any major improvement in the selection of work.

Recommended Articles

This is a guide to VBA Save Workbook. Here we discuss how to Save Workbook in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Month
  2. VBA On Error GoTo 0
  3. VBA On Error Resume Next
  4. VBA AND
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more