Copy last row to another sheet with validation...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Maybe this
VBA Code:
Sub CopyRow()
Dim lastrowSrc As Long, 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
 If Sheets("Coinbase").Range("B" & lastrowSrc) = Sheets("AllBuys").Range("B" & lastrowDest - 1) Then
    MsgBox "The row has already been copied !!"
    Exit Sub
 End If
 '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
 
Upvote 0
For the 2nd part try changing
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).EntireRow.Copy Sheets("AllBuys").Range("A" & lastrowDest)
to
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy Sheets("AllBuys").Range("A" & lastrowDest)
or
VBA Code:
    With Sheets("Coinbase")
        Intersect(.Range("A" & lastrowSrc).EntireRow, .Columns("A:L")).Copy Sheets("AllBuys").Range("A" & lastrowDest)
    End Sub
 
Last edited:
Upvote 0
Maybe this
VBA Code:
Sub CopyRow()
Dim lastrowSrc As Long, 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
If Sheets("Coinbase").Range("B" & lastrowSrc) = Sheets("AllBuys").Range("B" & lastrowDest - 1) Then
    MsgBox "The row has already been copied !!"
    Exit Sub
End If
'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
The code works a treat mate. Thanks. Sorry for the delay but it was a long day at work. Cheers.
 
Upvote 0
For the 2nd part try changing
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).EntireRow.Copy Sheets("AllBuys").Range("A" & lastrowDest)
to
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy Sheets("AllBuys").Range("A" & lastrowDest)
or
VBA Code:
    With Sheets("Coinbase")
        Intersect(.Range("A" & lastrowSrc).EntireRow, .Columns("A:L")).Copy Sheets("AllBuys").Range("A" & lastrowDest)
    End Sub
Hi buddy.
Your code works too. Much appreciated.
However, it is pasting the formulas on the fields with the formulas. In essense it is doing what I wanted to do but due to pasting formulas I am now facing crossrefeences, lol.
So in short, how do I put pastespecial so that only the cell values get pasted rather than the value but as a formula result, if it makes sense.
Much appreciated.
Thanks everyone and sorry for the late replay.

VBA Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

file
 
Last edited:
Upvote 0
If you want it to paste as Values then you change it to
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy 
Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
or
VBA Code:
    With Sheets("Coinbase")
        Intersect(.Range("A" & lastrowSrc).EntireRow, .Columns("A:L")).Copy 
        Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
    End Sub
 
Upvote 0
If you want it to paste as Values then you change it to
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy
Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
or
VBA Code:
    With Sheets("Coinbase")
        Intersect(.Range("A" & lastrowSrc).EntireRow, .Columns("A:L")).Copy
        Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
    End Sub
Brilliant mate.

I now need to look a bit more into how to add a unique ID automaticly on a collumn in the last empty row on diferent sheets.

I'm going to google away and then I'll be back I am sure.

Thanks ever so much for all your help people.

Much appreciated.

Cheers.
Albert
 
Upvote 0
Hi everyone.

This one I do not know how to go about it.

All data is being copied to AllBuys sheet. In there, on collumn B there's the Buy ID. now, I am using a formula on AllBuys sheet cell T1
Excel Formula:
=INDEX(B$3:B$1048576,COUNT(B$3:B$1048576))
to pick up the last value in that collumn and then on another cell T2 i just have T2=T1+1. This will give me my next ID number.

Now this cell is shown on every sheet so I can tell what's the next number to use in order not to repeat any.

I am trying to make this more automated but I am exhausted. It's been a long day at work today.
This excell sheet is slowly progressing and much, off course, with your help. Otherwise I wouldn't be able to do it at all.

So, much appreciated for all your help and, should anyone have any ideas as how to go about this please do share.

Much appreciated everyone and goodnight.

Cheers.
Albert
If you want it to paste as Values then you change it to
VBA Code:
Sheets("Coinbase").Range("A" & lastrowSrc).Resize(, 12).Copy
Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
or
VBA Code:
    With Sheets("Coinbase")
        Intersect(.Range("A" & lastrowSrc).EntireRow, .Columns("A:L")).Copy
        Sheets("AllBuys").Range("A" & lastrowDest).PasteSpecial xlValues
    End Sub
Hi Mark858.

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

I have tried to select the next empty cell in collumn A in hope that that would deselect the selected range but it didn't lol.

VBA Code:
Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Many thanks.
 

Attachments

  • Screenshot_646.png
    Screenshot_646.png
    12.2 KB · Views: 2
Upvote 0
At the end of the code add the line
VBA Code:
Application.CutCopyMode = False
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top