Excel Macro - referencing a cell containing a formula as a file name

[mattyblueice]

I have been trying to modify my macro to have it reference a cell (contains a formula) as the file name, but I just can't get it to work. The portion of the macro is:
ActiveWorkbook.SaveAs FileName: _
"C:\Users\Matt\Documents\Project Data\&"A20" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

The cell in A20 is this formula: CONCATENATE("Project Status ",TEXT(NOW(),"mm/dd/yyyy hh:mm am/pm")

The materials I have seem to indicate that this should work, but is it because the cell being referenced is a formula and not text? If so, anyway around that?

Thanks for your help!!!

Matt

 

[DanteAmor]

You cannot save files where the name contains signs such as: / :

Replace your formula for this:

=CONCATENATE ("Project Status ",TEXT(NOW(),"mm-dd-yyyy hh-mm am/pm"))

And your macro for this:

Sub test()
  Dim sPath As String
  Application.DisplayAlerts = False
  sPath = "C:\Users\Matt\Documents\Project Data\"
  ActiveWorkbook.SaveAs Filename:=sPath & Range("A20"), FileFormat:=xlOpenXMLWorkbook
End Sub

 

[JoeMo]

Filenames cannot contain the forward slash character "/". Try using:
TEXT(NOW(),"mm.dd.yyyy hh:mm am/pm")

 

[DanteAmor]

Hi Joe, Also the code must remove colon

TEXT(NOW(),"mm.dd.yyyy hh:mm am/pm")

 

[Joe4]

And you cannot reference the range A20 in your formula in VBA like "A20". That will literally put in the text value "A20".
You must use Range("A20")

 

[mattyblueice]

Yes Of Course!!!! Ugh - why didn't I see that before. HAHA - thanks so much

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.