get value the lastrow from sheet to another

[MKLAQ]

hi
i have data in two columns a,b the col a is date and b the values I would bring the value for last row based on date (today) and copy to cells f5 in sheet2
this is what I got so far

Sub vv()
Dim rows As String
Dim value As String
Dim concat As String

rows = Range("b1048576").End(xlUp).Row

Dim a As String
'Column
b = "b"
concat = a & rows
'Value in last low is below
value = Range(concat).value

MsgBox (value)
End Sub

 

[Joe4]

OK, I still need the answer to this question I asked up in post 7:

2. Where exactly on Sheet2 do you want these values copied over to?

I need to know the range address where you want this to show up!

 

[MKLAQ]

the range from e5:f5

 

[Joe4]

Try this:

Sub MyCopy()

    Dim lastRow As Long
    
    Sheets("Sheet1").Activate
    
'   Find last row with data in column A on Sheet1
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy values from columns B and C in last row to E5 and F5 on Sheet2
    Sheets("Sheet1").Range(Cells(lastRow, "B"), Cells(lastRow, "C")).Copy Sheets("Sheet2").Range("E5")
    
End Sub

 

[MKLAQ]

it's ver impressive ! well done ! but I would add condition it based only date (today) as in my pc is it possible?

 

[Joe4]

but I would add condition it based only date (today) as in my pc is it possible?

I am not sure I understand.
Through our conversations back and forth, I thought we determined that today's date will always be in the list (that is why I asked all those questions), and it will always be on the last row of Sheet1.
Is that not the case?

If not, can you post an example of another scenario, and explain what should happen in that case?

 

[Joe4]

This version of the code is longer, but will only copy over the last row in column A with today's date in it.

Sub MyCopy()

    Dim lastRow As Long
    Dim r As Long
    Dim cr As Long
    
    Sheets("Sheet1").Activate
    
'   Find last row with data in column A on Sheet1
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in Sheet1, starting from the bottom, until you find today's date
    For r = lastRow To 2 Step -1
        If Cells(r, "A") = Date Then
            cr = r
        End If
    Next r
    
'   See if it found today's date
    If cr > 0 Then
'       Copy values from columns B and C in last row to E4 and F4 on Sheet2
        Sheets("Sheet1").Range(Cells(cr, "B"), Cells(cr, "C")).Copy Sheets("Sheet2").Range("E4")
    Else
        MsgBox "Today's date not found in column A on Sheet1.", vbOKOnly, "ERROR!"
    End If
    
End Sub

 

[MKLAQ]

as far as I know when write this date (today) should mean current date and the current date is 02/10/2021 but if change the last row for more than current date or less than the code also bring them
your code design's for the value in last row with ignore the currently date
as in my picture in last row 02/19/2021 the the cells e5,f5 should are empty

I thought we determined that today's date will always be in the list

about this is my mistake should say not not the date today always is existed in lastrow and i'm sorry

date	brand	quantity
02/01/2021	as	12
02/02/2021	asd	12
02/03/2021	asd	123
02/04/2021	zx	1
02/05/2021	zz	12
02/06/2021	fg	34
02/07/2021	rt	23
02/08/2021	hh	12
02/09/2021	cf	12
02/10/2021	zx	15
02/11/2021	zx	16
02/12/2021	zx	17
02/13/2021	zx	18
02/14/2021	zx	19
02/15/2021	zx	20
02/16/2021	zx	21
02/17/2021	zx	22
02/18/2021	zx	23
02/19/2021	zx	24

 

[MKLAQ]

I tested your code there is still problem because in the lastrow is not date(today) then should e5,f5 are empty

 

[Joe4]

I tested your code there is still problem because in the lastrow is not date(today) then should e5,f5 are empty

OK, I was assuming that you wanted to look up the values in the table and find today's date.
(You are beginning to see now why complete, clear explanations are so important! While your problem is familiar to you, all that we have to go on is the little bit of information you provide to us here).

Here is code that I think will do what you want:

Sub MyCopy()

    Dim lastRow As Long
    
    Sheets("Sheet1").Activate
    
'   Find last row with data in column A on Sheet1
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Check to see if last row is today's date
    If Cells(lastRow, "A") = Date Then
'       Copy values from columns B and C in last row to E5 and F5 on Sheet2 if last row on Sheet1 is today
        Sheets("Sheet1").Range(Cells(lastRow, "B"), Cells(lastRow, "C")).Copy Sheets("Sheet2").Range("E5")
    Else
'       Blank out values on Sheet2 if last row on Sheet1 is not today
        Sheets("Sheet2").Range("E5:F5").ClearContents
    End If
    
End Sub

 

[MKLAQ]

astonishing ! !! I'm really sorry about my explanation is not clear but honestly I do my best to explain what I want , may be my language is not good to express by English I hope accept my apology if I waste your time
many thanks for your assistance