Variable workbook name in table call

[ekneen]

Hey all,
I'm trying to write a macro that copies data from a row in a different workbook to the given row in the current workbook. Help?

I have...

Please Login or Register  to view this content.

I want something along the lines of

Please Login or Register  to view this content.

[Leith Ross]

Hello ekneen,

Welcome to the Forum!

What problems are you having with the code?

[ekneen]

"Compile error:
Expected: end of statement"

Please Login or Register  to view this content.

That line is highlighted red.

[Leith Ross]

Hello ekneen

Maybe this is not relevant, In your original post you had

ActiveCell.FormulaR1C1 = "='101-Summary.xlsm'!Table1[Location]"

In the line that is faiiing you do not have = "101" prefix added.

ActiveCell.FormulaR1C1 = "" &Path& "-Summary.xlsm!Table1[Location]"

[ekneen]

Hi Leith,

The error line I posted is from the second block of the original code which shows what I want the code to act like. I am trying to replace the "101" from the line you just inputted with the value of a cell.

Thanks for your help!

[Leith Ross]

Hello ekneen,

Are your referring to this cell?

Please Login or Register  to view this content.

[ekneen]

Yes.

I want to make

Please Login or Register  to view this content.

work like...

Please Login or Register  to view this content.

Where Path is defined by...

Please Login or Register  to view this content.

[Leith Ross]

Hello ekneen,

Don't you need to add the equal sign back into the formula, like this...

Please Login or Register  to view this content.

[ekneen]

still throws the same error

[ekneen]

Resolved...

Please Login or Register  to view this content.