SaveAs with WeekNum to update the file name everyweek

[iwilli]

I download data from home office every week. I make a few edits and then need to save it a few times with different names for different uses later. My question this go around: Is it possible to have it save with the week number automatically updating each week? I thought this would work but I get an error as Excel cannot find the file. What am I doing wrong this time?

    Dim lrow As Long
    wk = Format(DateTime.Now, "ww")
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\iwilli\Documents\Region\CWA Region\Condensed_Weekly_Advisor_Results_by_Market_Group_or_Region-Week" & wk & "xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[JoeMo]

try defining wk like this:

wk = WorksheetFunction.WeekNum(Date, 1) 'Use 1 if week begins on Sunday, 2 for Monday

 

[iwilli]

-- removed inline image ---

Thanks JoeMo but I get the same Run-time error.

Run-time error '1004':
Microsoft Excel cannot access the file
'C:\User\iwilli\Documents\Region\CWA Results\38FED340'

Any other ideas?

 

[JoeMo]

-- removed inline image ---

Thanks JoeMo but I get the same Run-time error.

Run-time error '1004':
Microsoft Excel cannot access the file
'C:\User\iwilli\Documents\Region\CWA Results\38FED340'

Any other ideas?

Looks like you are missing a path separator. Maybe:

filename:= "C:\User\iwilli\Documents\Region\CWA Results\38FED340" & Application.PathSeparator & wk &".xlsx"

 

[iwilli]

Looks like you are missing a path separator. Maybe:

filename:= "C:\User\iwilli\Documents\Region\CWA Results\38FED340" & Application.PathSeparator & wk &".xlsx"

I changed the code and I get the same run-time error. thanks for the ideas though. I am just completely lost.