Find last row value from multiple sheets and copy those values to another sheet

Kerryx

Well-known Member
Joined
May 6, 2016
Messages
712
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
SheetList = Array("Sh1", "Sh2", "Sh3", "Sh4")
For Count = 0 To UBound(SheetList)
Dim lrow As Long
Sheets(SheetList).Select
Sheets(SheetList(Count)).Select
lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sh5").Select

ok got this far and the masg box shows last row values but having trouble on figuring out how to put those values in Sh5 column O , so it finds the last row in sheets 1 -4 , just want to paste the last row value into 4 cells starting at O1= lastrow hS1, O2=lastrow Sh2,O3= lastrow Sh3 and O4= lastrow Sh4.

It's used to check if extra rows are added to any of these sheets after updating from website to see if rest of vba script will run or not.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How about
Code:
SheetList = Array("Sh1", "Sh2", "Sh3", "Sh4")
For Count = 0 To UBound(SheetList)
    With Sheets(SheetList(Count))
        Sheets("Sh5").Range("O" & Count + 1).Value = .Range("A" & Rows.Count).End(xlUp).Row
    End With
Next Count
 
Upvote 0
got a runtime error 9 : subscript out of range but taking what you provided I changed the code as follows
SheetList = Array("Sh1", "Sh2", "Sh3", "Sh4")
For Count = 0 To UBound(SheetList)
Dim lrow As Long
Sheets(SheetList).Select
Sheets(SheetList(Count)).Select
lrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sh5").Select
Sheets("Sh5").Range("O" & Count + 1).value = lrow
Next

not pretty b lookslke it works
 
Last edited:
Upvote 0
In that case you can use
Code:
SheetList = Array("Sh1", "Sh2", "Sh3", "Sh4")
For Count = 0 To UBound(SheetList)
    With Sheets(SheetList(Count))
        Sheets("Team_Numbers").Range("O" & Count + 1).Value = .Range("A" & Rows.Count).End(xlUp).Row
    End With
Next Count
Using Select will slow down your code & is normally not needed.
 
Upvote 0
Perfect works like a charm, help much appreciated
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top