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:

Sub Macro1()

    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet4") ' Change to your sheet name
    
    Dim weeknum As String
    weeknum = ws.Range("A1").Value
    
    Dim person As String
    person = ws.Range("A2")
    
    Dim strPath As String
    strPath = "P:\" ' Change to your destination folder
    
    Dim strFileName As String
    strFileName = strPath & "Weeklist of " & person & " Week " & weeknum & ".xls" ' You can't use colons in file names
    
    
    wb.SaveAs strFileName, FileFormat:=51
    
    Set ws = Nothing
    Set wb = Nothing
    
    


End Sub

... 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! :-)