CODEWITHSUNDEEP
vbaexcel
Erik
Erik

Reputation: 9

Excel Save As Button Macro

I would like to create a VB "Save As" macro for Excel that would utilize the data from cell B7,B5 and =NOW as the file name. This new file name would then be saved to a particular directory. (Ex. User clicks "Save" button. File name = (B5)ABCD_(B7)EFGH_=NOW is created and then saved to a directory of my choosing.

I have found scripts that offer some of the singular options, but have had no luck finding or creating a script of my own with these options. Any help would be greatly appreciated.

Upvotes: 0

Views: 7311

Answers (1)

jonsca
jonsca

Reputation: 10381

You need to substitute for the invalid characters in the filename (they can't contain / or :) with periods or something else.

Sub DateFile()
    Dim str As String
    str = Range("B5").Value & "ABCD_" & Range("B7").Value & "EFGH" & Now()
    str = Replace(str, "/", ".")
    str = Replace(str, ":", ".")
    ActiveWorkbook.SaveAs (str)
End Sub

This can then be integrated into your push button code.

Upvotes: 2

Related Questions