ThatNewGuy question - updating sheets

I hope everyone had a wonderful Thanksgiving and spent quality time with your loved ones.

I'm hoping someone has some spare code laying around for this scenario. I'm very new to Excel VBA having done some work with Access before.

Scenario is update changes in dates from Sheet2 to Sheet1 and appending any new records in Sheet2 to Sheet1. Sheet1 will have approximately 3,000 rows maximum before it is archived.
Sheet2 will have approximately 600 records with maybe 50 new dates to update and 100 records to append. Not tied to any particular method of accomplishing this.

Hopefully this graphic will clarify my question:




Thanks for any help you can give! Happy Holidays!

View attachment ExcelGuruExample.xlsm

Here's the example sheet. (I think)

If you upload an actual spreadsheet, can show you an step by step solution. Otherwise, you can bring both tables into Power Query and perform a full Outer Join of the two tables.

alansidman said:

If you upload an actual spreadsheet, can show you an step by step solution. Otherwise, you can bring both tables into Power Query and perform a full Outer Join of the two tables.

Click to expand...

Thanks for your reply! Can't bring any add on's into our infrastructure so Power Query isn't an option. I would really appreciate your help with a step, by step solution.

This would be the actual sheet:

Sorry, I only see a PQ solution. If you are running 2016 or later, it is already part of your Excel version. I am sure that there are others here with a formula based solution but it is not me.

Thanks for looking through it. Hopefully someone will have a solution!

try:

p45cal said:

try:

Code:

Sub blah()
Set SceRng = Sheets("Sheet2").Range("A1").CurrentRegion.Resize(, 3)
Intersect(SceRng, SceRng.Offset(1)).Copy
With Sheets("Sheet1")
  .Range("A2").Insert Shift:=xlDown
  .Range("A1").CurrentRegion.Resize(, 3).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End With
End Sub

Click to expand...

That worked perfectly! All I had to add in was Dim SceRng as variant. Or should it be a string? Talk about minimal code that moves mountains.

Thank You!

It should be range

Dim xxxx as Range

RET said:

It should be range

Dim xxxx as Range

Click to expand...

Thanks!