Log In
Register
Home
December 4, 2021
Offline
I have a macro, and it works, however, it does not save the file to the correct folder. I have this code in my template, and the macro saves a copy for audit purposes. I would like the macro to save the audit file to the same location as the template is located. I do not want any hard-coded paths, as this template will be provided to multiple users to have on their computers. Thanks in advance for your help with this. I am sure it is something simple, but I have no clue.
Sub SaveAudit()
' Save file as multiple cell references
ThisFile = Range("B17").Value & Format(Range("B16").Value, "mm-dd-yyyy") & ".xlsb"
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
See Answer
Trusted Members
February 13, 2021
Offline
The path name would be ThisWorkbook.Path, and that will return the path where the file is saved for each computer.
December 4, 2021
Offline
@Jessica,
I thought I understood your response, but apparently not. I revised my code, and when I ran it, I got an error. Attached is a screenshot of the error, and the location where is showed when I attempted to debug.
Sub SaveAudit()
' Save file as multiple cell references
Dim ThisFile As String
Dim Path As String
ThisFile = Range("B17").Value & Format(Range("B16").Value, "mm-dd-yyyy") & ".xlsb"
Path = ThisWorkbook.Path & "\" & ThisFile
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
Trusted Members
February 13, 2021
Offline
Your issue is this code right here:
ActiveWorkbook.SaveAs Filename:=ThisFile
I don't work with saving in VBA often, but I believe what you want instead is this:
ActiveWorkbook.SaveAs Filename:=Path
When saving with only the filename you want you haven't given it the path of where to save it.
Answers Post
1 Guest(s)
