SaveAs With Path Macro Help

[Mathman]

I'm have trouble getting this to work properly. Would anyone be able to help? I can't seem to get it working

Thank you!



Sub Save_Workbook()


'Save current file in current location
ThisWorkbook.Save

'Always automatically replace existing file with same name
ThisWorkbook.Save
to path C:\Users\canad\OneDrive\Documents\Testing\Master Template

"Adds Date at the front of existing file name
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & VBA.Format(VBA.Date, "YYYY-MM-DD") & "_" & ThisWorkbook.Name
to path =H:\Testing\Master Template


"Adds Date & Time at the front of existing file name
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & VBA.Format(VBA.DateTime, "YYYY-MM-DD HH:MM") & "_" & ThisWorkbook.Name
to path E:\Indicator Tests\-1 Testing Backup\Master Template


End Sub​

 

[JackDanIce]

Untested, try:

Sub SaveWbk()

    Dim msg As String
    
    With ThisWorkbook
        msg = .Name & "_@T.xlsm"
        .Save
        .SaveAs "C:\Users\canad\OneDrive\Documents\Testing\Master Template\" & Replace(msg, "_@T", "")
        .SaveAs "H:\Testing\Master Template\" & Replace(msg, "_@T", Format(Date, "YYYY-MM-DD"))
        .SaveAs "E:\Indicator Tests\-1 Testing Backup\Master Template" & Replace(msg, "_@T", Format(Now, "YYYY-MM-DD HH:MM"))
    End With
    
End Sub

 

[Mathman]

Thank you that's a great step forward! I need a few changes if possible.



*** this ends up looking like this book1.xlsm.xlsm

.SaveAs "C:\Users\canad\OneDrive\Documents\Testing\Master Template" & Replace(msg, "_@T", "")


*** How would I get the date first then name. 2019-08-25 Book1.xlsm
.SaveAs "H:\Testing\Master Template" & Replace(msg, "_@T", Format(Date, "YYYY-MM-DD"))

This returns an error and would also like it to be

2019-08-25 9:02AM Book1.xlsm​

.SaveAs "E:\Indicator Tests\-1 Testing Backup\Master Template" & Replace(msg, "_@T", Format(Now, "YYYY-MM-DD HH:MM"))

Is this possible?
​

 

[JackDanIce]

Changes highlighted, try:

Sub SaveWbk()

    Dim msg As String
    
    With ThisWorkbook
        msg = "_@T" & .Name
        .Save
        .SaveAs "C:\Users\canad\OneDrive\Documents\Testing\Master Template\" & Replace(msg, "_@T", "")
        .SaveAs "H:\Testing\Master Template\" & Replace(msg, "_@T", Format(Date, "YYYY-MM-DD"))
        .SaveAs "E:\Indicator Tests\-1 Testing Backup\Master Template" & Replace(msg, "_@T", Format(Now, "YYYY-MM-DD H:MM AM/PM"))
    End With
    
End Sub

 

[Mathman]

Nice the date is now well placed thank you.

I keep getting an error for this line and cant seem to know why?

.SaveAs "E:\Indicator Tests\-1 Testing Backup\Master Template" & Replace(msg, "_@T", Format(Now, "​

YYYY-MM-DD H:MM AM/PM​

"))​

 

[JackDanIce]

What is the error?

 

[Mathman]

lol real douzy. cant save when using : to name a sheet. so I did

YYYY-MM-DD H-MM-SS AM/PM. ​

I think I have it worked out. Thanks for your help, much appreciated!

 

[Mathman]

One last thing. Is it possible to stop the pop up from asking if I want to replace file? I would like it to always replace file automatically without asking.

 

[JackDanIce]

Try adding these lines:

Application.DisplayAlerts = False

With ThisWorkbook
.
.
.
End With

Application.DisplayAlerts = True

 

[Mathman]

Looks good thanks again. I made a few changes and seems to work. If anything looks out of place or could be better written please do let me know.


Sub Save_New1()
Dim msg As String
Application.DisplayAlerts = False

With ThisWorkbook

msg = "_@T " & .Name
msg1 = "_@T" & .Name
Date1 = Application.WorksheetFunction.RoundDown(Now() - Weekday(Now(), 3) + 6, 0)
Date2 = Now()

.Save

'One drive
.SaveAs "C:\Users\canad\OneDrive\Documents\Testing\Master Template" & Replace(msg, "_@T", "")

'External Hard Drive
.SaveAs "H:\Testing\Master Template" & Replace(msg, "_@T", Format(Date1, "YYYY-MM-DD"))

'Hard Drive saves all wanted Changes
.SaveAs "E:\Indicator Tests\-1 Testing Backup\Master Template" & Replace(msg, "_@T", Format(Date2, "yyyy-mm-dd hh-mm-ss AM/PM"))

'Brings back to original file/location
.SaveAs "E:\Indicator Tests" & Replace(msg1, "_@T", "")

End With

Application.DisplayAlerts = True
End Sub