Comparing two list with dynamic list range

[jadnat2k]

Sheet1 and Sheet2 has 2 column that consists of Names and Values

Sheet2 is where I dump a report too

My macro basically compares sheet2 with sheet1 and replaces the old value from Sheet1
The problem im running into is that I can't figure out a way to make my range selection dynamic in my macro,
for example if someone added a new row on Names it wouldn't be able to compare that new row or name because my macro has the range selection fixed.

any help would be appreciated
here's my codes...

    Columns("D:D").Select
    Range("D4").Activate
    Selection.UnMerge
    Sheets("DATA").Select
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0))),0,INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0)))"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H11"), Type:=xlFillDefault
    Range("H3:H11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DATA DUMP").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("DATA").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select

 

[Datsmart]

This code will work if you don't have other data down the page in column H:

Sub CopyData()
    Columns("D:D").UnMerge
    'Set Variable for LastRow in Column H
    LR = Sheets("DATA").Cells(Sheets("DATA").Rows.Count, "H").End(xlUp).Row
    Sheets("DATA").Range("H3").FormulaR1C1 = _
        "=IF(ISNA(INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0))),0,INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0)))"
    Range("H3").AutoFill Destination:=Range("H3:H" & LR), Type:=xlFillDefault
    
    With Sheets("DATA")
        .Range("H3:H" & LR).Copy
        .Range("H3").PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    
    Sheets("DATA DUMP").Cells.Delete Shift:=xlUp
End Sub

 

[jadnat2k]

Thanks for the help Datsmart...

Here's my revised code...it works perfectly...
Now I would like to know how to insert a row if the report(Sheet2) has a name that's not on the old list (Sheet1)...

Dim LR As Variant

    Columns("D:D").UnMerge
    Sheets("DATA").Select
    
    'Get the last row address
    LR = Range("G1").End(xlDown).Row

    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0))),0,INDEX('DATA DUMP'!C[5],MATCH(RC[-1],'DATA DUMP'!C[-4],0)))"
    Range("H3").Select
    Selection.AutoFill Destination:=Range("H3:H" & LR), Type:=xlFillDefault
    Range("H3:H" & LR).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DATA DUMP").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("DATA").Select
    Range("A1").Select