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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,132
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

MARK858

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

albertc30

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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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:

MARK858

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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome, happy we could help.
 

albertc30

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,132
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
At the end of the code add the line
VBA Code:
Application.CutCopyMode = False
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,149
Members
416,959
Latest member
Mohzein

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