How to run the same macro separately in all worksheets

[Ramadan2512]

I have a workbook with multiple sheets and I have a simple macro to go to the last row in the sheet

Sub LastRow()

    Range("amberville").Select
    Selection.End(xlDown).Select

End Sub

my problem is that when i copy the macro button to other sheet in the same workbook it doesn't work unless I change the range name "amberville" but when i do this the button in the other sheet change automatically to the new range name

please let me know how to make the same macro run separately in each sheet

 

[Joe4]

Firstly, you can combine your code down to a single line of code, like this:

Sub LastRow()
    Range("amberville").End(xlDown).Select
End Sub

Secondly, are you really looking for the last row on the sheet, or in your named range?
Are there different named ranges on every sheet?
If so, how is the code to know that the range name for a particular sheet is? Is there any logic to it?

If you truly want to find the last row on each sheet, if there is always data in column A for every row with data, you could simply use something like this:

Sub LastRow()
    Cells(Rows.Count, "A").End(xlUp).EntireRow.Select
End Sub

which is generic enough to use on every sheet, so you could just have this code once, in a General module, and run it from any sheet.

 

[Ramadan2512]

Firstly, you can combine your code down to a single line of code, like this:

Sub LastRow()
    Range("amberville").End(xlDown).Select
End Sub

Secondly, are you really looking for the last row on the sheet, or in your named range?
Are there different named ranges on every sheet?
If so, how is the code to know that the range name for a particular sheet is? Is there any logic to it?

If you truly want to find the last row on each sheet, if there is always data in column A for every row with data, you could simply use something like this:

Sub LastRow()
    Cells(Rows.Count, "A").End(xlUp).EntireRow.Select
End Sub

which is generic enough to use on every sheet, so you could just have this code once, in a General module, and run it from any sheet.

thank you so much for your reply - actually i have 9 worksheets in the workbook and each one has a data in table named "amverville" , "carnell" , "westridge"..etc...
I have tried your last code but it gave me the first row in the sheet and when i changed this line Cells(Rows.Count, "A").End(xlUp).EntireRow.Select to be .End(xlDown) it worked but it gives me the last row in the sheet not in the table

can you please fix it to be in table not in the entire sheet

thank you in advance

 

[Joe4]

Is your table in the same place on every sheet?
What cell does it start in (the upper, left-most cell address)?

 

[Alex Blakenburg]

Give this a try:

Sub LastRow()

    ActiveSheet.ListObjects(1).ListColumns(1).Range.End(xlDown).Select

End Sub

 

[Ramadan2512]

Give this a try:

Sub LastRow()

    ActiveSheet.ListObjects(1).ListColumns(1).Range.End(xlDown).Select

End Sub

Yes all tables are the same except only the data inside
now it works perfectly thank you soooooooooooo much Alex Blakenburg I do appreciate your qucik help

 

[Alex Blakenburg]

You're welcome. Glad we could help.