Copy Workbook & Save to Specific Location

[torourke17]

Hello, need a little help, hopefully it's relatively easy. I currently have a code that saves my workbook to a specific folder I designated, naming the file based off a cell value. Unfortunately once I click the button to run the Macro it saves the current workbook to that location. I would prefer that it saves a copy to the pathway, keeping my 'active' workbook untouched and also closes the copy that was made. Below is the code I've been using.

Sub filename_cellvalue()
Dim Path As String
Dim filename As String
Path = "G:\xxx\yyy\bbb\etc"
filename = Range("B162")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=52
End Sub

 

[Logit]

.
Here is a macro that will accomplish your goal. You will need to edit some of the code to match your path, name, etc.

Option Explicit


Sub sveWrkBk()
Dim FName, x, y     As String
Dim FPath           As String


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    x = Sheets("Sheet1").Range("C1").Value
    y = Sheets("Sheet1").Range("C2").Value
    Sheets("Sheet1").Range("C1:C2").Value = ""
    ActiveSheet.DrawingObjects.Visible = False
    FPath = "C:\Users\logit\Desktop\"          ''<--- Change SAVE DIRECTORY as required
    ThisWorkbook.SaveCopyAs Filename:=FPath & x & y & ".xlsm"
    
    'Application.Visible = False            '<-- Uncomment line if you want this workbook to close after saving
    'Application.Quit                       '<-- UntrueFalse
    ActiveSheet.DrawingObjects.Visible = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub