Excel VBA to Save as File Using Path from Cell (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to apply Excel VBA to save as a file using a path from a cell. In Excel, we can easily save a file from the Home tab. But today, we will use VBA to do it. Here, a cell in our sheet will contain the path location and we need to save the file in that path location using VBA. We shall discuss the whole method in quick steps. So, without any delay, let’s start the discussion.


Excel VBA to Save as File Using Path from Cell: Step-by-Step Procedures

To explain the steps easily, we will use a workbook that contains a worksheet. The worksheet contains information about the height and weight of a person. It also stores the path location in a cell. You will see the dataset in STEP 1. Let’s follow the steps below to learn the method.


STEP 1: Make Dataset Ready

  • First of all, you need to make the dataset ready.
  • Select a cell and type or paste the path location. Because you need to assign the cell in the VBA code later.
  • Here, we are using a dataset of height and weight. From the dataset, you can see that Cell B14 contains the path location.

Step-by-Step Procedures of Excel VBA to Save as File Using Path from Cell

Read More: How to Save a Copy as XLSX Using Excel VBA


STEP 2: Open Visual Basic Editor

  • Secondly, you need to open the Visual Basic Editor.
  • To do so, go to the Developer tab in the ribbon and select Visual Basic.
  • Alternatively, you can press Alt + F11 to open it.
  • Moreover, if you are a laptop user, then press Fn + Alt + F11 to open the Visual Basic Editor.

Step-by-Step Procedures of Excel VBA to Save as File Using Path from Cell


STEP 3: Module Window Opening

  • In the third step, open the Module window from the Visual Basic window.
  • For that purpose, click on the Insert tab and select Module. It will open the Module window.

Step-by-Step Procedures of Excel VBA to Save as File Using Path from Cell


STEP 4: Type VBA Code

  • In the Module window, type the code below:
Sub Save_as_File_Using_Path()
Dim Wks1 As Worksheet
Dim Wkb1 As Workbook
Dim xPath As String
Dim FileName As String
Dim Wks2 As Worksheet
Dim Wkb2 As Workbook
Set Wkb1 = ThisWorkbook
Set Wks1 = Wkb1.Worksheets("Weight Info")
xPath = Wks1.Range("B14")
FileName = xPath & "\" & Wks1.Range("B2") & ".xlsx"
Wks1.Range("B2:C11").Copy
Set Wkb2 = Workbooks.Add
Set Wks2 = Wkb2.ActiveSheet
With Wks2.Range("B2:C11")
.PasteSpecial (xlPasteValues)
.PasteSpecial (xlPasteFormats)
Wks2.Range("B2:C11").Columns.AutoFit
End With
Application.DisplayAlerts = False
Wkb2.SaveAs FileName:=FileName, FileFormat:=xlWorkbookDefault
Wkb2.Close
Application.DisplayAlerts = True
End Sub

Step-by-Step Procedures of Excel VBA to Save as File Using Path from Cell

Here, this code will create a copy of the active workbook and save it as a file in the path location stored in a cell.

VBA Code Explanation

Dim Wks1 As Worksheet
Dim Wkb1 As Workbook
Dim xPath As String
Dim FileName As String
Dim Wks2 As Worksheet
Dim Wkb2 As Workbook

Here, Wks1 and Wkb1 are the variables to indicate the active worksheet and workbook respectively. Wks2 and Wkb2 represent the new worksheet and workbook that will be saved in the path location. xPath and FileName denote the path location and name of the file respectively.

xPath = Wks1.Range("B14")

This command is used to denote the path location. It means the value of Cell B14 of the active worksheet will be the path location. You need to change the path location when you will work with this code on your PC. Otherwise, it won’t work.

FileName = xPath & "\" & Wks1.Range("B2") & ".xlsx"

Another interesting thing is that we wanted to save the file as an excel file. So, we have used the FileName = xPath & “\” & Wks1.Range(“B2”) & “.xlsx” command. If you want to save it in .xlsm format, then type .xlsm in place of the .xlsx of this command.

Wks1.Range("B2:C11").Copy

We just wanted to copy range B2:C11 from the current sheet. So, we have used the Wks1.Range(“B2:C11”).Copy command. Change it according to your needs.

With Wks2.Range("B2:C11")
.PasteSpecial (xlPasteValues)
.PasteSpecial (xlPasteFormats)
Wks2.Range("B2:C11").Columns.AutoFit
End With

We have used this portion to paste the values into the new sheet and to AutoFit column’s width.

Application.DisplayAlerts = False
Wkb2.SaveAs FileName:=FileName, FileFormat:=xlWorkbookDefault
Wkb2.Close
Application.DisplayAlerts = True

In the last portion, we used commands to save the file as a workbook.

Read More: Excel VBA Save as File Format


STEP 5: Save and Run VBA Code

  • Now, you need to save the code first and then, run it.
  • To save the code, simply hit Ctrl + S on the keyboard.
  • After that, press the F5 key to run the code.
  • Otherwise, navigate to the Developer tab and select Macros. The Macro window will appear after this.

Step-by-Step Procedures of Excel VBA to Save as File Using Path from Cell

  • From the Macro window, select the desired code and click on Run.


STEP 6: Check Saved File in Path Location

  • After running the code, open the folder that was denoting the path location in Cell B14.
  • In the desired location, you will see the excel file.

Read More: Excel VBA: Save Workbook as New File in Same Folder


STEP 7: View Saved as File with Microsoft Excel

  • Finally, open the Excel file and you will see the dataset. It indicates that the VBA code actually worked.


Download Practice Book

Download the practice book here.


Conclusion

In this article, we have demonstrated step-by-step procedures for Excel VBA to Save as a File Using a Path from Cell. I hope this article will help you to perform your tasks easily. Moreover, you can use the same code to save the file. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo