anglais428
Well-known Member
- Joined
- Nov 23, 2009
- Messages
- 634
- Office Version
- 2016
- Platform
- Windows
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:
<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:
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.
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:
Code:
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.