Dynamic File Name Referencing
 

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

Dynamic File Name Referencing
 

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...

On Oct 30, 10:20 am, wrote:
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

Dynamic File Name Referencing
 

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

" wrote:

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