Create a macro for "save as" and "print"

[josullivan601632]

I want to use a "SAVE AS" and "PRINT" icon (already placed in spreadsheet) and assisgn a macro for each; I rightclick the image and "assisgn a macro", > new ...but this is where I get stuck, everything I try does not work! For the "Save As" I want the file to be given the numerical content of cell A2 (currently a numerical value) + plus current date and be saved in a specified folder.

For the print, I just want the user to be able to click on the icon and the 3 page spreadsheet be printed.

I believe the above is possible but I am not clever enough

Any suggestion would be appreciated.

 

[NoSparks]

Do these tasks manually with the macro recorder running and see what/how Excel does it.
It won't be exactly what you want but a very good place to start.

 

[josullivan601632]

Thank you, I have got so far and learnt alot! Printer one I have done works great.

The Save As I am struggling with recording the macro from the save button I have inserted. I have a code that I think I'm getting close with, not sure how to "word" as the macro I am recording has a differerent name, I have:

Sub SaveAsA2()

ThisFile=Range("A2"). (Value)&Format, "mm-dd-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Fileman:=ThisFile

End Sub

Any guidance to correc this, I am sure its in the language!

 

[NoSparks]

Sub SaveAsA2()

thePath = ThisWorkbook.Path & "/"
thisFile = Range("A2").Value & "-" & Format(Date, "mm-dd-yyyy")
fullFilename = thePath & thisFile

ActiveWorkbook.SaveAs fullFilename, 52

End Sub

put your cursor in the macro on the word SaveAs and hit F1 to see the VBA help file and find out why the 52 and no .xlsm

 

[josullivan601632]

Thank you so much for our response. I am being a bit thick obviously because I don't understand. I copied the code, it returned an error as proof I did it wrong!! where it says 'fullFilename' do I overtype 'Saved Buyers Worksheets' which is the folder I want it saved to, where is say 'Path' do I overtype the path 'SHARED (\\DiskStation) (P'

 

[NoSparks]

you type the full path to the folder you want it saved in on the right side of the thePath= equal sign
you type the name you want for the file on the right side of the thisFile= equal sign

fullFilename is a variable that uses the other two variables so you don't have to type it all out in the SaveAs line

 

[josullivan601632]

Thanks, so this is what I have:

Sub SaveAsA2()

thePath = P:\Buyers Files\Saved Buyers Worksheets
thisFile = Range("A2").Value & "-" & Format(Date, "mm-dd-yyyy")
fullFilename = thePath & thisFile

ActiveWorkbook.SaveAs fullFilename, 52

End Sub

Results in:
Compile error:
Syntax error

Have I misunderstood you?

 

[NoSparks]

likely missing the separator "/" on the end of thePath

 

[josullivan601632]

This is the marco, copied and pasted:-

Sub SaveAsA2()

thePath = P:\Buyers Files\Saved Buyers Worksheets\
thisFile = Range("A2").Value & "-" & Format(Date, "mm-dd-yyyy")
fullFilename = thePath & thisFile

ActiveWorkbook.SaveAs fullFilename, 52

End Sub

Still says
Compile error:
Syntax error

Would anybody be prepared to test this for me?