Save Copy As to a different folder

[daren.beaney]

Hi, I've got this code that allows me to create a backup copy of my workbook however I can't find any code that I can include that will save the copy to defined folder location e.g. "G:\My Documents\Temp\AMP\Archive"

I'm sure it's easy but I can't find anything that seems to work with this code structure

This is the code as it stands which saves a copy into the same folder location (AMP):

Sub SaveArchive()
Dim sFileName As String
    Dim sDateTime As String


    With ThisWorkbook
        sDateTime = " (" & Format(Now, "dd-mm-yy hhmm") & ").xlsm"
        sFileName = Application.WorksheetFunction.Substitute _
          (.FullName, ".xlsm", sDateTime)
         
        .SaveCopyAs sFileName
    End With


End Sub

Thanks in advance!

 

[John_w]

    ThisWorkbook.SaveCopyAs "G:\My Documents\Temp\AMP\Archive\" & Replace(ThisWorkbook.Name, ".xlsm", " (" & Format(Now, "dd-mm-yy hhmm") & ").xlsm")

 

[daren.beaney]

Hi John, thanks for this, it worked fine when tested locally, however when I place the actual required destination, it saves it in the file location (01 Stakeholders) any idea what's preventing it saving to the Archive folder - it does exist.

Sub Archive()
ThisWorkbook.SaveCopyAs "F:\Adfact\Adelphi Modernisation\Business Change - Comms\01 Stakeholders\Archive" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Format(Now, "dd-mm-yy hhmm") & ").xlsm")
End Sub

Thanks
Daren

 

[John_w]

Add a back slash at the end of "Archive"

 

[daren.beaney]

Thanks John, that worked a treat! I should have realised that based on the filename included 'Archive'.

Do you by any chance know how to include a userID into the file name based on the code I'm using?

 

[John_w]

Call the Environ function to get the USERNAME environment variable:

Sub Archive()
ThisWorkbook.SaveCopyAs "F:\Adfact\Adelphi Modernisation\Business Change - Comms\01 Stakeholders\Archive" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Environ("USERNAME") & Format(Now, " dd-mm-yy hhmm") & ").xlsm")
End Sub

 

[daren.beaney]

Call the Environ function to get the USERNAME environment variable:

Sub Archive()
ThisWorkbook.SaveCopyAs "F:\Adfact\Adelphi Modernisation\Business Change - Comms\01 Stakeholders\Archive\" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Environ("USERNAME") & Format(Now, " dd-mm-yy hhmm") & ").xlsm")
End Sub

Thanks John, that worked perfectly!