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

[NessPJ]

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?

 

[mumps]

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]

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).

 

[mumps]

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]

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]

I'm sorry but my antivirus software blocks the site you posted. Could you use box.com or dropbox.com?

 

[NessPJ]

Ah...

I put it on dropbox: https://www.dropbox.com/s/2a2g89ynj28hc7g/example.xlsx?dl=0

 

[mumps]

Try:

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

 

[NessPJ]

Try:

This works like a charm, thanks a lot!

 

[mumps]

You are very welcome.