Copy last row to another sheet with validation...

[albertc30]

Hi all.

I hope this post will find you all in good health and having a greta weekend thus far.

I am now working on making my crypto excel sheets more automated and, as usual I have come to a wall.

I have managed the code bellow to copy the last row of current sheet and have it added to a sheet called AllBuys.

Sub CopyRow()
Dim lastrowSrc As Long
Dim lastrowDest As Long
 'Get last row of data
 lastrowSrc = Sheets("Coinbase").Range("A" & Rows.Count).End(xlUp).Row
 
 'Get first blank row (last row of data +1)
 lastrowDest = Sheets("AllBuys").Range("A" & Rows.Count).End(xlUp).Row + 1
 
 'Copy row
 Sheets("Coinbase").Range("A" & lastrowSrc).EntireRow.Copy Sheets("AllBuys").Range("A" & lastrowDest) 'I also wish not to copy the entire row but only till collumn L.
End Sub

Now the issue is that on collumn there's an ID number that the macro should cross reference to see if the last line has already been copied.
At the moment it's not doing any validation and it just repeatedly adds the data onto the AllBuys sheet.

How do I go about making sure the macro looks for the data in collumn B on AllBuys and if it finds the same value it returns a "Line already copied msg!".

Also, I don't need to copy the entire row, only data from collumn A though collumn L.

Any advise is much appreciated as always.

Kind regards,
Albert.

 

[MARK858]

One thing your code is doing is to leave the selection highlighted. Anyway to once data pasted have that gone?

Place the line

Application.CutCopyMode = False

after the PasteSpecial line.

Edit: Michael beat me to it

 

[albertc30]

Oh boy!

It was all going so great! It seems values are being duplicated due to the only looking at the last line on the AllBuys sheet.

I now see that it would have to look at the entire collumn B (AllBuys) and if it finds the current row data on cell B then it won't copy.

This is because there are two diferent sets of sheets but from different exchanges.

I'll leave a copy of the file here if anyone would like to have a better look at and understand what I mean.

I now must force myself to bed so a reply might take some time.

Much appreciated everyone, for your time and kindness. You're always willing to help others.

Cheers.

ExcelFile

 

[MARK858]

Hi Albert. If you are posting screenshots of sheets it is a lot better for us if you can use the boards XL2BB Addin as we can't copy/paste into Excel from an image for testing.

The XL2BB icon in the reply window is a link to the download and instructions on how to install and use the Addin.

If you can post using the XL2BB addin then I will have a look at it tomorrow if no-one else responds.

 

[albertc30]

Hi Mark.

Much appreciated.

I will have a look later but for the meantime and if you can or works, here's LINK to the file.

Out for work now.

Everyone have a great day and be safe.
Albert.

 

[MARK858]

Maybe give the below a try

Sub CopyRowCB()

    Dim lastrowSrc As Long, lastrowDest As Long, lastrowVal As String, DestChk As Range

    'Get search value
    lastrowVal = Sheets("Coinbase").Range("B" & Rows.Count).End(xlUp).Value
 
    ' test if value exists
    Set DestChk = Sheets("AllBuys").Columns("B:B").Find(lastrowVal, , xlValues, , xlByRows, xlNext)
    
    If DestChk Is Nothing Then
 
        'if value doesn't exist
        'Get last row of data
        lastrowDest = Sheets("AllBuys").Range("A" & Rows.Count).End(xlUp).Row + 1
        lastrowSrc = Sheets("Coinbase").Range("A" & Rows.Count).End(xlUp).Row
 
        'do copy if value doesn't exist
        Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy
        Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
        Sheets("AllBuys").Range("A" & lastrowDest).NumberFormat = "dd/mm/yyyy"
        Application.CutCopyMode = False
        
    Else

        'if value does exist
        MsgBox "The row has already been copied !!"
        Exit Sub
    End If
  
End Sub

 

[albertc30]

Maybe give the below a try

Sub CopyRowCB()

    Dim lastrowSrc As Long, lastrowDest As Long, lastrowVal As String, DestChk As Range

    'Get search value
    lastrowVal = Sheets("Coinbase").Range("B" & Rows.Count).End(xlUp).Value

    ' test if value exists
    Set DestChk = Sheets("AllBuys").Columns("B:B").Find(lastrowVal, , xlValues, , xlByRows, xlNext)
   
    If DestChk Is Nothing Then

        'if value doesn't exist
        'Get last row of data
        lastrowDest = Sheets("AllBuys").Range("A" & Rows.Count).End(xlUp).Row + 1
        lastrowSrc = Sheets("Coinbase").Range("A" & Rows.Count).End(xlUp).Row

        'do copy if value doesn't exist
        Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy
        Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
        Sheets("AllBuys").Range("A" & lastrowDest).NumberFormat = "dd/mm/yyyy"
        Application.CutCopyMode = False
       
    Else

        'if value does exist
        MsgBox "The row has already been copied !!"
        Exit Sub
    End If
 
End Sub

Hi Mark.

Sorry for the late reply.

It works a charme mate.

Much, much appreciated for your kindness and time not only given to myself but to the group.

I don't think I will ever amount to any decent minimal amount of programming, lol.

Again, many thanks mate.

Thanks to you all good people always ready to give a helping hand.

Be safe and good night.

Cheers.
Albert

 

[MARK858]

You're welcome

I don't think I will ever amount to any decent minimal amount of programming, lol.

Give it time, we learn at different rates.