Dynamic File Name Referencing

[lukegunther]

Hey guys, Hoping someone can get back to me with this one.

The problem i have is I have created a macro such that a file is saved
using the following command:

ActiveWorkbook.SaveAs Filename:=Range("Sourcefile).Value &
Format(Date, "mmdd")

which should give me a file named Sourcefile1007.xls

The issue I have is using the same referance within another workbooks.
I want a cell to referance/equal sourcefile(mmdd)sheetx!A1.

I am unable to successfully write a function which will add the mmdd
values and return the correct cell value.

Any help would be greatly appreciated.

Thanks,

Luke

 

[Faisal...]

Luke

Can you try this instead?:
ActiveWorkbook.SaveAs Filename:=Range("Sourcefile).Value &
Format(Month(XX),"00")& Format(Day(XX),"00")

Where XX is the reference to your date value.

B.R,
Faisal...

 

[Joel]

try this

Sub test()

Const path = "c:\temp\"
Const sourcefile = "abc"

Const mydate As Date = "1/1/07"

stringdate = Format(mydate, "mmdd")
Filename = sourcefile & stringdate & ".xls"
myformula = "='[" & Filename & "]sheetx'!A1"

Range("B1").Formula = myformula

End Sub