Formula for Linked Worksheet Last Row

[shuffine]

Hi! I am trying to find the last row of worksheet "A", return the value, then in Worksheet "B", reference Worksheet "A" Column J - last row. It gives me an error for the formula. I don't know if I am close to the solution, or way off. Here is my code:

<code>
Dim LastRow As Long
Sheets("NICMap31 Data").Select 'goes to worksheet A
Range("A1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'finds the last row of worksheet A
Sheets("NIC MAP Data Table").Select 'returns to worksheet B
Range("C7").Select
ActiveCell.formula = "='NICMap31 Data'!(J & LastRow - 1)" 'link to worksheet A with this formula.

</code>

I really appreciate the help!!

 

[Andrew Poulsom]

LastRow is a variable so it needs to be outside the double quotes. Try:

ActiveCell.Formula = "='NICMap31 Data'!J" & LastRow - 1

 

[shuffine]

Thanks again Andrew! I was wondering if it had something to do with my quotes and/or parenthesis. You are awesome - thanks!