Copying new values into table

[BiggusDoggus]

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.

 

[BiggusDoggus]

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]

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.

 

[BiggusDoggus]

Bump. Anybody able to help please?