Macro to change filename in another macro

[BBRed]

Hi,
I have a macro that opens a file, selects columns, copies and pastes on another file, saves file, then closes both files. I have to run this Marco every month. The way I run it every month is I go into the macro and find and replace the date with the new date. For example: C:My Documents: Asset Schedule 0815 needs to change to C:My Documents: Asset Schedule 0915. So what I do is I go into the VBA code and Find 0815 and Replace it with 0915. So my question is, instead of doing this manually, can I make another Marco that will change my filename in my original Macro? It would be really nice if I can change it using a cell reference so if my cell says 0915 it will change all the file names using that reference. Thanks in advance!!

 

[krausr79]

what if you replaced
"C:My Documents: Asset Schedule 0815"
with
"C:My Documents: Asset Schedule " & Right("0" & Month(Date), 2) & Right(Year(Date), 2)
Or
"C:My Documents: Asset Schedule " & "Sheets("Sheet1").range("B2").value

 

[BBRed]

This could definitely work but I tried option 2 and it keeps highlighting "sheet1" It's on sheet 1.

 

[krausr79]

Er line 2 has an extra quote.. More like:

"C:My Documents: Asset Schedule " & Sheets("Sheet1").range("B2").value

Otherwise you could look into named ranges and do something like range("YourRangeName").value