• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

New Member
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

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.
 

Attachments

Last edited by a moderator:

Kenneth Hobson

Active Member
Run Main() from Sheet.
Code:
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

Excel Ninja
Paste this demonstration to the (Sheet1) worksheet module :​
Code:
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

New Member
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.
 

Attachments

Top