VBA Save File with automatic name

047yneedss · Jan 30th 2011

I have the below code to save the quotation, and it asks what to save the file as within a InputBox.

Is there a way of this being automatically generated so Q0001, Q0002 and so on ...

Code

Sub Save_Quotation()


MyFilename = InputBox("Enter quotation number") & ".xls"
MyDir = "..\Desktop\NT System\BackEnd\Private Hire\Quotations"
ActiveWorkbook.SaveAs Filename:= _
MyDir & "\" & MyFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End Sub

Display More

Bill Rockenbach · Jan 30th 2011

Re: VBA Save File with automatic name

Use a helper cell for a counter then assign the filename like this

Code

Range("A1") = Range("A1") + 1
MyFilename = "Q" & Format(Range("A1"), "0000#")

VBA Save File with automatic name

Participate now!

Share