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

Kerryx

Board Regular
Joined
May 6, 2016
Messages
78
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,891
Office Version
365
Platform
Windows
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
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
78
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,891
Office Version
365
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,891
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,019
Messages
5,411,871
Members
403,403
Latest member
BBleaz

This Week's Hot Topics

Top