Saving with vba with current user spefic directory

[Mattm3486]

Hello,
Still new to excel and needing a little help. I have a workbook that i need to have the user save weekly into their archive folder located within the same folder the workbook is held, however the file path changes depending on the user. I know i had it close as i can get it to save into the save folder as the workbook but not into the archive folder located in the same directory.
Workbook is in :
G:\_OVERDRAFT\AMF Project Files\Tracker\**Current user**
and inside this directory is the folder archive.

what i have without it needing to change users is below (user is Matt)

ActiveWorkbook.SaveAs Filename:= _
"G:\_OVERDRAFT\AMF Project Files\Tracker\Matt\Archive\Function Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Any help would be appreciated , thank you

 

[DanteAmor]

Hi @★ Mattm3486,

Try this

Sub test()
  Dim path1 As String, UserName As String, path2 As String, sFile As String
  path1 = "G:\_OVERDRAFT\AMF Project Files\Tracker\"
  UserName = Environ("username")
  sFile = "Function Sheet - " & Format(Date, "dd-mm-yyyy") & ".xlsm"
  path2 = path1 & UserName & "\Archive\" & sFile
  ActiveWorkbook.SaveAs path2, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub