Results 1 to 10 of 10

Thread: ThatNewGuy question - updating sheets

  1. #1
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13

    ThatNewGuy question - updating sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    Name:  ExcelGuruImage.jpg
Views: 108
Size:  43.1 KB



    Thanks for any help you can give! Happy Holidays!

  2. #2
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13
    ExcelGuruExample.xlsm

    Here's the example sheet. (I think)

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    283
    Articles
    0
    Excel Version
    2019
    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.

  4. #4
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13
    Quote Originally Posted by alansidman View Post
    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.
    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:
    Attached Files Attached Files

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    283
    Articles
    0
    Excel Version
    2019
    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.

  6. #6
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13
    Thanks for looking through it. Hopefully someone will have a solution!

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,844
    Articles
    0
    Excel Version
    365
    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

  8. #8
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13
    Quote Originally Posted by p45cal View Post
    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

    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!

  9. #9
    Seeker RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    10
    Articles
    0
    Excel Version
    2019
    It should be range

    Dim xxxx as Range

  10. #10
    Seeker ThatNewGuy's Avatar
    Join Date
    Nov 2020
    Posts
    6
    Articles
    0
    Excel Version
    13
    Quote Originally Posted by RET View Post
    It should be range

    Dim xxxx as Range
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •