Reference other Workbook Through Macro Trouble

[TKB]

Hello,

I am creating a macro that will automatically set cell A1 in the current workbook to cell A1 in another workbook through the following macro

ActiveCell.FormulaR1C1 = "='" & Stn & "\[" & NEmployee & ".xlsx]Sheet1'!A1"

While the formula does what it should, somewhere during the macro instead of reading:

='P:\TimeCards\[Rosa.xlsx]Sheet1'!A1

it reads like:

='P:\TimeCards\[Rosa.xlsx]Sheet1'!'A1'

I need to get rid of the ' highlighted in red and I don't know how to...

Is there a way to find those two ' and delete them?

Here is the surrounding code:

Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A1").Select


    ActiveCell.FormulaR1C1 = "='" & Stn & "\[" & NEmployee & ".xlsx]Sheet1'!A1"
    Range("A1").Select

'Then it just goes through and cleans up a bunch of non referenced cells

 

[TKB]

Any ideas?

 

[daverunt]

Hi,

removing the R1C1 stops the '!' but what the effect on your code is I don't know.

ActiveCell.Formula = "='" & Stn & "\[" & NEmployee & ".xlsx]Sheet1'!A1"

 

[TKB]

yea, i have no idea why it was doing that. I had to go through and put the R & C for each cell :/. O well, i guess it at least works now... even tho its not very compact.

Thanks Deverunt!