VBA compare two worksheets and update only two columns

[kraw]

I need to update two columns comparing sheet1 and sheet2.

Sheet1


Sheet2


This script works perfectly:

>>> use - code tags <<<

Sub DATA1()
Const L = 2
Dim Rf As Range, Rg As Range
Application.ScreenUpdating = False

With Sheet1.Cells(1).CurrentRegion.Columns
   Set Rg = .Item(1):  F& = .Count - L + 1
End With

With Sheet2.Cells(1).CurrentRegion
        C& = .Columns.Count - L + 1
    For R& = 2 To .Rows.Count
        Set Rf = Rg.Find(.Cells(R, 1).Value, , xlValues, xlWhole)
        If Not Rf Is Nothing Then Rf(1, F).Resize(, L).Copy .Cells(R, C)
    Next
End With
           Set Rf = Nothing:  Set Rg = Nothing
End Sub

I have beautifully updated the last two columns.

Sheet2



Right now, I need to update only two columns between Sheet3 and Sheet4.

Sheet3


Sheet4



This script works, but it is updating the last 5 columns.
I need to update only two -> G and H

Could you guys help me modify the below script to have updated only columns G and H?

>>> use code - tags <<<

Sub DATA1()
Const L = 5
Dim Rf As Range, Rg As Range
Application.ScreenUpdating = False

With Sheet3.Cells(1).CurrentRegion.Columns
   Set Rg = .Item(1):  F& = .Count - L + 1
End With

With Sheet4Cells(1).CurrentRegion
        C& = .Columns.Count - L + 1
    For R& = 2 To .Rows.Count
        Set Rf = Rg.Find(.Cells(R, 1).Value, , xlValues, xlWhole)
        If Not Rf Is Nothing Then Rf(1, F).Resize(, L).Copy .Cells(R, C)
    Next
End With
           Set Rf = Nothing:  Set Rg = Nothing
End Sub

 

[vletm]

kraw
Hint: Instead of sending snapshots
it's more useful to send a sample Excel-file.
Especially for testing ...

Question: Have You tried to run Your lower copy&pasted-code?
... because there seems to be at least one typo, which will give an error.

 

[kraw]

vietm,
I have attached the original file.

 

[vletm]

kraw
You skipped my question ...
Why Your files code is ... a bit different that above 'copy&pasted'?
What is missing or should be other ways after run Your ... new code?
= Could You show expected results with that sample data -- even manually written.

 

[kraw]

There was a typo... missing dot. Sorry about that.
The correct code is below.

Sub DATA1NEW()
Const L = 5
Dim Rf As Range, Rg As Range
Application.ScreenUpdating = False

With Sheet5.Cells(1).CurrentRegion.Columns
   Set Rg = .Item(1):  F& = .Count - L + 1
End With

With Sheet6.Cells(1).CurrentRegion
        C& = .Columns.Count - L + 1
    For R& = 2 To .Rows.Count
        Set Rf = Rg.Find(.Cells(R, 1).Value, , xlValues, xlWhole)
        If Not Rf Is Nothing Then Rf(1, F).Resize(, L).Copy .Cells(R, C)
    Next
End With
           Set Rf = Nothing:  Set Rg = Nothing
End Sub

I have also attached a file with code and sample data.

Expected results. Only columns G and H are updated.



My code is updating the last five columns instead of only G and H, and I don't know how to modify that.

 

[vletm]

kraw
If run again that code for DATA2NEW-sheet (as with previous file) it gives:

How do You ... explain ... expect ... those values in range I2:K4 ?

 

[kraw]

vletm,
As you can see below sheets DATA1NEW and DATA2NEW have different values in columns L,J and K.
I want to update only columns G and H in sheet DATA2NEW. So these (new) values (columns I, J, and K) in DATA2NEW should be untouched.

DATA1NEW


DATA2NEW


Expected values in DATA2NEW
Only columns G and H were updated and I have no idea how to do that.


Right now when I run my code you can see updated columns from G to K. I don't want that. I want to keep data in columns I, J, and K untouched.


Thank you.

 

[vletm]

kraw
I can see ...
There were those 'double' numbers (range DATA2NEW!I2:K4) then I opened Your file.
As I have tried to write ... only range G2:H4 changed then I run Your macro.
Who knows - who has added those there - not Your code!
... but if Your purpose is to update range G2:H4 too as in DATA1NEW-sheet
... then that would be something else than You've written (keep it untouched)!
If You can give something ... new
... then I could try to help
... otherwise ... take care.

 

[jerry_maguire]

Try this:

Compare Two Columns and Highlight Matches

1. Select the entire data set.
2. Click the Home tab.
3. In the Styles group, click on the 'Conditional Formatting' option.
4. Hover the cursor on the Highlight Cell Rules option.
5. Click on Duplicate Values.
6. In the Duplicate Values dialog box, make sure 'Duplicate' is selected.
7. Specify the formatting
8. Click OK.

Hope this is useful.

Kind Regards,
Jerry.

 

[kraw]

Thank you so much for all of your responses.
I have solved that problem.
It was a small change in line 14.
The working code is below.

Sub DATA1NEW()
Const L = 5
Dim Rf As Range, Rg As Range
Application.ScreenUpdating = False

With Sheet5.Cells(1).CurrentRegion.Columns
   Set Rg = .Item(1):  F& = .Count - L + 1
End With

With Sheet6.Cells(1).CurrentRegion
        C& = .Columns.Count - L + 1
    For R& = 2 To .Rows.Count
        Set Rf = Rg.Find(.Cells(R, 1).Value, , xlValues, xlWhole)
        If Not Rf Is Nothing Then Rf(1, F).Resize(1, 2).Copy .Cells(R, C)
    Next
End With
           Set Rf = Nothing:  Set Rg = Nothing
End Sub

 

[vletm]

kraw
Seems that You have challenges to give answers.
Take care, as I wrote.