Copy last row to another sheet with validation...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
One thing your code is doing is to leave the selection highlighted. Anyway to once data pasted have that gone?

Place the line
VBA Code:
Application.CutCopyMode = False
after the PasteSpecial line.

Edit: Michael beat me to it
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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
 

Attachments

  • Screenshot_647.png
    Screenshot_647.png
    28.7 KB · Views: 3

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Maybe give the below a try
VBA Code:
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
 
Solution

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
Maybe give the below a try
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,518
Messages
5,636,806
Members
416,941
Latest member
shazzaxyz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top