Macro to run from all sheets and extract the last cell value of each table of respective sheets and fill the final table on sheet 1

[Mysore]

Problem: I have multiple sheets which contain varying number of rows and columns which contain numeric data. Value of my interest from each table is is last cell (last row and last column) highlighted in excel irrespective of table size. I Need to copy this last cell value from each long data sheets onto sheet 1 as shown against respective sheet numbers.

I have tried below VBA code

Dim lRow As Integer
Dim lCol As Long
   
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Value
                          
Range("BD71").Copy Worksheets("Sheet1").Range("F5")

The problem with this code is I need to repeat this for every sheet. so please suggest/guide so that i don't have to rerun the same code for each sheet separately.

 

[Kenneth Hobson]

Run Main() from Sheet.

Sub Main()
  Dim c As Range
  For Each c In Range("E5", Range("E5").End(xlDown))
    c.Offset(, 1) = LastCell(c.Value)
  Next c
End Sub

Function LastCell(ws As String) As Range
  Set LastCell = Worksheets(ws).Cells.Find(What:="*", _
    After:=Worksheets(ws).Range("A1"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False)
End Function

 

[Marc L]

Paste this demonstration to the (Sheet1) worksheet module :​

Sub Demo1()
    Dim V, R&
        V = Range("E5", [E4].End(xlDown)).Value2
    For R = 1 To UBound(V)
        V(R, 1) = Sheets(V(R, 1)).UsedRange.Find("*", , xlValues, , xlByColumns, xlPrevious).Value2
    Next
        [F5].Resize(UBound(V)).Value2 = V
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Mysore]

Thank you It's working!

 

[Mysore]

with continuation to that..
I want to update K5/K6/K7 with the difference from last week's value. Could you guys help me please.
As this data will be refreshed every week so I want to capture what is the difference (increased (+1/+2..)/decreased (-1/-2..) / Or no change (0)) like that.

 

[Marc L]

No need a code, just use an easy formula …​

​