Excel Macro Save.As Date

Question

I have a Macro that takes my Master workbook & saves a bunch of different copies with different labels.

Example of section for one workbook:

Range("E1:G1").Select
    ActiveCell.FormulaR1C1 = "099 Commercial"
    ChDir "T:\Accounting\Aspen Pre-Payroll (Shared)\_Payroll Emails"
    ActiveWindow.View = xlNormalView
    ActiveWindow.Zoom = 50
    ActiveWorkbook.SaveAs Filename:= _
        "T:\Accounting\Aspen Pre-Payroll (Shared)\_Payroll Emails\099 Payroll Bundle CREW & EXP.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

So the label after the macro saves it looks like 099 Payroll Bundle CREW & EXP But I would like it to look like 099 Payroll Bundle 08-23-2016 CREW & EXP with the 08-23-2016 being the date located in the specific cell =RC[-1] However, the usuals " & Format(Date, "MM-DD-YYYY") & " & " & Format(Now(), "MM-DD-YYYY") & " will not work & gives me a compile error pop-up.

My knowledge about macros is limited so any help would be appreciated!

EDIT: ERROR it gives when I try to run macro

try lowercase `" & Format(Now(), "mm-dd-yyyy") & "` and post the code that gives the error — Slai, Aug 23 '16 at 21:13
It wouldn't let me post it so I had to add it to the end of my original post as an EDIT — SWinchester, Aug 23 '16 at 21:26
ah I see .. then it has to be `" & VBA.Format(Now(), "mm-dd-yyyy") & "` — Slai, Aug 23 '16 at 21:31

Rory · Answer 1 · 2016-08-23T21:34:13.530

0

Try using Application.Text (replacing 'now()' with your date)

ActiveWorkbook.SaveAs Filename:= _
        "T:\Accounting\Aspen Pre-Payroll (Shared)\_Payroll Emails\099 Payroll Bundle" & Application.Text(Now(), "MM-DD-YYYY") & " CREW & EXP.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

edited Aug 23 '16 at 21:34

answered Aug 23 '16 at 21:28

Rory

413
1
4
16

If I use this: ActiveWorkbook.SaveAs Filename:= _ "T:\Accounting\Aspen Pre-Payroll (Shared)\_Payroll Emails\098 Payroll Bundle" & Application.Text (=RC[-1], "MM-DD-YYYY") & " CREW & EXP.xlsx" _ , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False it just gives me a syntax error & turns that section of text red – SWinchester Aug 23 '16 at 21:38
It won't be able to interpret =RC[-1]. is the range dynamic, or could you specify it as `sheetname.range("A1")`, for example? – Rory Aug 23 '16 at 21:42
or alternatively, store the date as a variable and refer to it as `Application.Text(yourDate, "MM-DD-YYYY")` – Rory Aug 23 '16 at 21:43

Excel Macro Save.As Date

1 Answers1