Hello,

I have a macro in a workbook that saves 3 separate files based on the names of 3 sheets (RESTAURANT, LOUNGE, ROOM SERVICE) in the workbook. It does this only on Fridays with an IF statement.

I would like the files saved as "RESTAURANT Week Ending mm.dd.yy", "LOUNGE Week Ending mm.dd.yy", and "ROOM SERVICE Week Ending mm.dd.yy" where mm.dd.yy is the week ending date that corresponds to a named RANGE("DATE") on another sheet called HOME.

Here is the code I currently have. I have tried to do this but am unsuccessful. Is there a way do to this? Thank you for any assistance.

Sheets("HOME)").Select
If Range("DATE") = "Friday" Then

Dim Sheet As Worksheet, SheetName
Dim ws As Worksheet


For Each ws In Worksheets(Array("RESTAURANT", "LOUNGE", "ROOM SERVICE"))

    SheetName = ws.Name
    ws.Copy
    
        
    With ActiveWorkbook
        .SaveAs "C:\Users\JT\Desktop\Work\SavedPayrollFiles\" & SheetName & ".XLS"
        .Close SaveChanges:=True
    End With
     
  Next ws

End If