Intuitive Macro that changes based on cell value

[DonFlak]

I don't know if this is possible but I am hoping so. I run several performance reports daily and weekly. On Monday's I have to run the reports twice, once to compile the prior week recap and the second time to produce the current weeks performance. Currently between runs, I have to archive the prior files and reset the data sources. Basically, I open Data2 and resave it as Data1, so the macro will look to the correct data source. In my Report Dashboard I have a fiscal week indicator, currently 35.

I have a macro that will save the prior weeks files with a reporting week in the title.

EXAMPLE:
ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\Report_WK34.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The code works fine but of course I have to manually change the week number each week. I am looking for a macro that would look at the fiscal week indicator in my dashboard

Sheets("Dashboard").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "35"

and change the macro to match the indicator Dashboard, Cell C1, so I can eliminate this manual process.

Is this even possible and if so, HELP! How can I achieve this?

 

[Andrew Fergus]

Hi

How about something like this:

ActiveWorkbook.SaveAs Filename:= _
"P:\Finance\central\Finance\Daily\Report_WK" & Sheets("Dashboard").Range("C1").Value & ".xls" _ etc......

Andrew

P.S. as per usual save a copy and test before you deploy live.