[VBA] Put Table1 and Table2 together (override similarities from Table1) ?

NessPJ

Active Member
Joined
May 10, 2011
Messages
385
Office Version
  1. 365
Hello all,

I have a sheet with 3 tables:
B15:J4000 (source)
L15:T3511 (updates)
V15:AD7011 (new)

The first columns in all these tables contain a 'key' value.
I would like to transfer the contents of table B15:J4000 to the table V15:AD7011, but table L15:T3511 contains updates for only some of the values found in table B15:J4000. I would like to put all available contents from table L15:T3511 in table V15:AD7011 as well, but if a line with the same key already exists it should be overwritten by the line of data from table L15:T3511.

Is this possible? :)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
When you say
transfer the contents of table B15:J4000 to the table V15:AD7011
do you want to simply copy the contents below the data that already exists in table V15:AD7011?
 

NessPJ

Active Member
Joined
May 10, 2011
Messages
385
Office Version
  1. 365
Hello there,

Well the table B15:J4000 and L15:T3511 are renewed with data weekly.
The first step in the VBA could perhaps be to copy all of the available contents from table B15:J4000 to table V15:AD7011 sure, but the data from L15:T3511 should be added to table V15:AD7011 after that and overwrite any similar keys/lines that are found in table V15:AD7000 (originating from table B15:J4000).
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
I think I get the general idea of what you want to do but I need some clarification. Are the three tables all on one worksheet or are they in 3 different worksheets? If they are on 3 separate sheets, what are the 3 sheet names? Do you want to copy table B15:J4000 to table V15:AD7011 below table V15:AD7011 or to range B15:J4000? Do you want to overwrite only the values with similar keys/lines in the data from table B15:J4000 or also overwrite values with similar keys/lines in the data from table V15:AD7011? It is always easier to help and test possible solutions and see how your data is organized if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 

NessPJ

Active Member
Joined
May 10, 2011
Messages
385
Office Version
  1. 365

ADVERTISEMENT

Hey,

I have an example file here:
http://www40.zippyshare.com/v/Ad5baKeV/file.html

(Could never figure out how to add an attachment through these forums so i used Zippyshare).

All contents from Table1 should be copied to Table3.
All contents from Table2 should be copied to Table3 as but overwrite any lines with the same key.

Thanks for all the help so far. :)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
I'm sorry but my antivirus software blocks the site you posted. Could you use box.com or dropbox.com?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,562
Try:
Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    Dim bottomL As Long
    bottomL = Columns("L").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
    Dim key As Range
    Dim foundKey As Range
    Range("B15:J" & bottomB).Copy Cells(Rows.Count, "V").End(xlUp).Offset(1, 0)
    For Each key In Range("L15:L" & bottomL)
        Set foundKey = Range("V15:V" & Cells(Rows.Count, "V").End(xlUp).Offset(1, 0).Row).Find(key, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundKey Is Nothing Then
            Range("L" & key.Row & ":T" & key.Row).Copy Cells(foundKey.Row, "V")
        Else
            Range("L" & key.Row & ":T" & key.Row).Copy Cells(Rows.Count, "V").End(xlUp).Offset(1, 0)
        End If
    Next key
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,021
Messages
5,526,299
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top