Macro Merge

[Ody]

Hello again,

I'm having trouble merging the following macros:

'go to the first blank row in table of summary sheet
Sheets("Summary").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select

with

'reference info from last worksheet to summary sheet
Dim LastSheet As String
LastSheet = Worksheets(Worksheets.Count).Name
Worksheets("Summary").Range("A11").Formula = "='" & LastSheet & "'!J1"

I realized this morning that the range for the second macro is hard coded to A11 of my summary sheet and my attempts to make that range the next blank row in my table instead of A11 is falling flat. Any and all help is appreciated. Thanks.

 

[Ody]

bump. Thanks.

 

[John_w]

Maybe:

'reference info from last worksheet to summary sheet
Dim LastSheet As String
LastSheet = Worksheets(Worksheets.Count).Name
With Worksheets("Summary")
  lMaxRows = .Cells(.Rows.Count, "A").End(xlUp).Row
  .Range("A" & lMaxRows + 1).Formula = "='" & LastSheet & "'!J1" 
End With

 

[Ody]

Thanks John!

It's frustrating knowing what you want to do and not being able to do it. THe macro recording only takes me so far.

Thanks again!