Macro to save a file with a pre file name + week number from a cell

Hi there,

i want to make a save as macro

the save file needs to have a pre set filename but it needs to include the weeknumber from a cell
in the cell i use a formula to get the weeknumber from a date.

Example:
when i click a macro button called Save File, there should be a save file created at a pre set location like a Desktop folder with a filename like Weeklist: including the weeknumber from lets say cell a1
so the filename will be: Weeklist: 49.xls


is it also possible to get the text of 2 different cell's
example:
a1 contains the weeknumber
a2 contains the name of a person

Filename will be Weeklist of: (a2) Week: (a1).xls

Try this:

Please Login or Register  to view this content.

... but, you can't use colons ( in file names.

the code you send me does what i mean but i get an error in

wb.SaveAs strFileName, FileFormat:=51

Error: the following features can not be saved in macro-free workbooks?

Oh, yeah, that'll happen if you are using ".xls" (FileFormat:=51) and the workbook has a macro in it. You can either switch to ".xlsm" (FileFormat:=52), or you can have the macro copy all the worksheets into a new book, without the macros, and do the SaveAs on the new workbook, instead. Simpler just to change the file extension in the name and the format in the existing, but pretty easy the other way, too.

Thnx it worked exactly as i wanted

Wonderful! Glad I could help!

Yeah, click my star, too! :-)