Concatenate with Macro

[Ody]

Hello all,

I'm trying to get my concatenate macro to work but I'm running into issues because the range is always on my last sheet. I'm trying to modify the following code unsuccessfully:

'reference headliners from last worksheet to summary sheet
lMaxRows = .Cells(.Rows.Count, "j").End(xlUp).Row
.Range("j" & lMaxRows + 1).Formula = "='" & LastSheet & "'!bx7" & LastSheet "'!bx8"

I can't figure out how to always point my macro to the last sheet then combine the two cells I want in my summary.

When I use the macro recorder I get this:

"=CONCATENATE('11-1111'!R[-9]C[72],CHAR(10),CHAR(10),'11-1111'!R[-8]C[72])"

Many thanks for any help.

 

[Ody]

Any thoughts? Thanks...