VBA How to save workbook with a new name in the same file as original?

[julierae]

After running the macro, I want to save a new copied workbook with a filename using several cells in the worksheet + date. This file will be used by multiple people so the path will be different by user. I was hoping to rename the workbook and when saved, it would be in the same folder as the original workbook. I tried:

ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Copy

ThisWorkbook.SaveCopyAs Sheets("Sheet2").Range("A21") & " " & Sheets("Sheet2").Range("A18") & " " & Format(Now(), "DD-MMM-YYYY") & ".xlsx"

However, it is not saving it in the same folder. It saves in a random folder with the correct name. Also, the copied version of the workbook is open but does not reflect the new name. Is there a way to have the new file save in the same folder as the original file and to have the new opened document reflect the new name?

 

[tonyyy]

julierae,

Welcome to the Board.

You might consider the following...

Sub SaveAs_1061438()
Dim fPath As String, fName As String

With ThisWorkbook
    fPath = .Path
    fName = .Sheets("Sheet2").Range("A21") & " " & .Sheets("Sheet2").Range("A18") & " " & Format(Now(), "DD-MMM-YYYY") & ".xlsx"
    .Sheets(Array("Sheet1", "Sheet2")).Copy
End With
ActiveWorkbook.SaveAs FileName:=fPath & "\" & fName
End Sub

The code captures the workbook path with the .Path property, then uses that to save the new/copied file.

Cheers,

tonyyy

 

[julierae]

Thank you, that did what I needed. I really appreciate your help!

 

[tonyyy]

You're very welcome, julierae...