VBA Tweak

[diatex]

Hello

I am using this snippet of vba to print two copies, would it be possible to amend this so the "save as" box also appeared after clicking the command button.

Sub PrintAndNumberInvoice()
Range("A1").Value = Range("A1").Value + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
End Sub

Thanks

Diatex

 

[tactps]

The easy way to is put in a input box for the user to type the name required as follows:

FileName1 = InputBox("Please input filename", "Filename")
ChDir "c:\"
ActiveWorkbook.SaveAs Filename:="C:\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

 

[CraigM]

Put this line at the end of your procedure:

Application.Dialogs(xlDialogSaveAs).Show

 

[diatex]

Thanks for the quick response CraigM & tactps, both fit the bill.

I have been playing around trying to enter the full file path in tactps vba, so i can save to a folder called downloads on c:\ but i must be missing something somewhere. Any chance of an ammendment then i can go to bed without my brain working overtime all night
thanks again men
Diatex

 

[tactps]

FileName1 = InputBox("Please input filename", "Filename")
ChDir "c:\downloads\"
ActiveWorkbook.SaveAs Filename:="C:\downloads\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

This will automatically save it in "downloads" without having to type the full path.

Otherwise, get rid of the "c:\" statement in the savas line, and just leave "FileName1". Then type full path (untested but should work)

 

[diatex]

Thanks tactps

never noticed the SaveAs Filename:="C:\ on the third line obviously looking too hard. Now i can go to bed

goodnight or is it good morning for you

Thanks
diatex

 

[tactps]

Also change line 2 (will work faster).

Sleep well and no dreaming of macros!!!!

Noon here - {so maybe time for me to have a sleep too!}