Copying new values into table

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
Hi all

Hoping you can help, please!

So I have a newly created table in sheet 1. In real world, it has a number of other columns, which includes the need to manually input information into some of them, but for this purpose lets focus on the two that really matter -

Column A :Date
Column B: Ref number

Sheet 1 pulls this data from sheet 2. At the end of each day the table is to be sorted by date - the manually entered information, of course, needs to remain against the right date and ref number.

Sheet 2 is where a dump of data is going to be entered on a daily basis. Same columns/data as sheet 1. However, the new data dump each day will include all the previous day's data as well. And, unfortunately, the nature of the data being gathered means that the dates aren't always sequential. For example, data received on the 25th will include references not just from the 24th, but from the 3rd, or the 11th, etc.

So a simple vlookup etc wont work.

What I need to happen is the first reference number that is in sheet 2 that doesn't already exist in sheet 1 needs to be copied into sheet 1, at the bottom, after the last entry. Along with the date. Then the next one, then the next one. And so on.

So, for example, row 100 in sheet 1 has 23/11 in Row A, 556677 in Row B.

Reference 556677 is already in sheet 2, but 223344 is new, so needs to be copied into row 101, column B, along with the date 16/11 in column A.

I hope this makes sense. I suspect it's going to require VBA rather than a formula in the cells in sheet 1, but I hope not as I have very little knowledge of VBA! But if that is what it takes...

thanks.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
Update - I have come across this code:

Sub CopyData()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim rng As Range
Dim foundVal As Range
For Each rng In Sheets("Sheet2").Range("A1:B" & LastRow)
Set foundVal = Sheets("Sheet1").Range("B:B").Find(rng, LookIn:=xlValues, LookAt:=xlWhole)
If foundVal Is Nothing Then
rng.EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next rng
Application.ScreenUpdating = True


End Sub



But it only pastes the first two rows of Sheet2, at the end of the existing data in Sheet1, and then stops? And those two ref numbers already exist in Sheet 1 as well.
 

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
Update to the update

Using the above, if I change For Each rng In Sheets("Sheet2").Range("A1:B" & LastRow to A50 (for example), it pastes the first 50 rows. Still in the right place, at least, but also still not unique ref numbers - they all exist already in Sheet1.

Sorry if I'm sounding like a numpty - as I said, I don't know VBA very well at all, so don't know what I need to change to make this work.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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