Cell linking: Destination cell must perform a search for data

**r2fro** · 01-08-2014, 10:33 AM

Hello there,

I have a macro which copies and pastes data from cells in one sheet to another.

However, I need one of the destination cells to perform a kind of "search" function in order to remain updated (the data origin cell will not always be the same).

The target cell must be able to find and select the data from the original sheet.
Like this:

Range("*Original Worksheet*!C1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1").Select

Can anyone help with this?

I will try and provide as much extra information as I can upon request.
Thanks

**r2fro** · 01-09-2014, 05:50 AM

UPDATE:

I have created a formula which performs the same function of the "search" macro above which can be easily pasted into the destination cell using:

ActiveCell.Formula = "=INDIRECT( ADDRESS( MATCH( 9.99E+306,F:F, 1),6, 4) )"

But my question now is:

How do I make the values F:F correspond to the active worksheet?
The same macro will be used for hundreds of worksheets so I somehow need the active worksheet name to be inserted to the formula.

Any help would be hugely appreciated .