Issue creating a furmula in VBA, Please Help

[Guest]

I am trying to write a formula for a range that links data Sheet1(Cashflows
PBEY) to Sheet2EY Cashflows), the data in sheet1 is linked to a sheet I
copied from another workbook, the code below for some reason brings up like
an open file dialog with a title of "Update Values: PBEY" and is prompting me
to select a file to open??? I've used this code before and never sow this
below, can this be because the original data is on a sheet I copied from
anothr workbook? Please help me out, thanks.

Sheets(EYCF).Select
'With Sheets(EYCF)


TopAdd = Range(Cells(StartRow, eycfcusip), Cells(StartRow, eycfcusip)).Address
BottomAdd = Range(Cells(intNumOfTrDesc + HdrRow, eycfcusip),
Cells(intNumOfTrDesc + HdrRow, eycfcusip)).Address
Range(TopAdd, BottomAdd).name = "temp"
Range("temp").Formula = "=" & Sheets(CFPBEY).name & "!" & "RC"

I want sheet2!A1 to be linked to sheet1!A1 and all the way down.

 

[Dave Peterson]

If you created that formula manually, you'd see that excel required that the
sheet name be surrounded by apostrophes. And if you're using the R1C1 reference
style, you may want to use .formular1c1, too:

Maybe it's as simple as:
Range("temp").FormulaR1C1 = "='" & Sheets(CFPBEY).name & "'!" & "RC"

 

[Guest]

thanks Dave, it worked, but it's weird because I have an almost identical
formula in another macro and it works, oh well!

 

[Dave Peterson]

It'll depend on the name of the worksheet. But if you always include the
apostrophes, your code will work--whether they're needed or not.

And excel/vba is pretty forgiving with the .formular1c1 v. .formula stuff. But
why let it forgive, just use the one that's appropriate (don't let excel guess
at what you want).