VBA reference sheet name dynamically

[anglais428]

I have the following issue:
1) I need to get the last row used in each worksheet (I have this working)
2) I need to add the last row used value to a master sheet.

The master sheet looks like:

Sheet name	data range
Sheet 1	$A$2:$J$5
Sheet 2	$A$2:$J$10

<tbody>
</tbody>

"$A$2:$J$" will remain static - with the last row value added at the end for each sheet.

I would like to add a formula to compute the data ranges.
So far I have:

LR = Cells(Rows.Count, "A").End(xlUp).Row

For cl = 2 To LR


Cells(cl, 2).Value = "$A$2:$J$" & Sheets([B]"Sheet 1"[/B]).Cells(Rows.Count, "A").End(xlUp).Row


Next cl

which works fine for the first data record (i.e. Sheet 1 -> $A$2:$J$5). However, it obviously will put the same data range beside Sheet 2 (instead of $A$2:$J$10).
I would like to be able to replace the "Sheet 1" and insert something dynamic so that when the code jumps to the next line it automatically changes from referencing "Sheet 1" to "Sheet 2".

Thanks.

 

[anglais428]

Just figured it out by adding Sheetname = Cells(cl, 1).Value

 

[Fluff]

How about

Sub anglais428()
   Dim i As Long
   
   For i = 1 To Worksheets.Count
      With Worksheets(i)
         Sheets("Master").Range("A" & i + 1) = .Name
         Sheets("Master").Range("B" & i + 1) = "$A$2:$J$" & .Range("A" & Rows.Count).End(xlUp).Row
      End With
   Next i
End Sub

 

[anglais428]

Thanks Fluff. That works too.

 

[Fluff]

You're welcome & thanks for the feedback