match two excel spread sheet using macro

[Lillian Lian]

I have two excel spreed sheet, sheet1 and sheet2 both has same information as
following:
IPAddress, Domain, Username, machinename

sheet1 has 900 records.
sheet2 has 1100 records.

I would like match IPaddress on columnA on sheet1 and sheet2, if they are
match then then move all the sheet1 900 records to sheet2 on columnF,G.H.I

how can I write the macro for this?

Thank you so much

Lillian

 

[Otto Moehrbach]

Lillian
This macro will loop through all the cells in Column A of sheet1 and
look for that value in Column A of Sheet2. If the value is found, it will
copy Columns A of that row from sheet1 and paste it to the next empty cell
in Column F of sheet2. Post back if this is not what you want. HTH Otto
Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is
Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub

 

[Lillian Lian]

Otto,

I copy your macro to sheet1, but it has compiler error on

Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))

what is that means?

Thank for the help

Lillian

 

[Otto Moehrbach]

Lillian
I don't know without seeing your code. Perhaps you are a victim of word
wrapping. In your actual code,
Set RngSht2ColA = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
should all be on one line. You will get an error if it is on two lines.
HTH Otto

 

[Lillian Lian]

Otto,

I following your instruction, but I realize that sheet2 repeatedly copy
by himself.
on sheet1 has 900 records.
on sheet2 has 1100 records.
I look at sheet2 from column F to I it show 1100 records as well, I thought
only 900 records moved to sheet2 of Column F to I.

any idea?

Lillian

 

[Otto Moehrbach]

Lillian
I thought you wanted to copy only those records in Sheet1 that appeared
in Sheet2 and the data copied to be the 4 columns in sheet1 to Column F of
sheet2. Is that not what you wanted? You may be having this problem
because of what sheet is the active sheet. Sheet1 must be the active sheet
when you run the macro. If you wish, copy this line
Sheets("Sheet1").Select
and place it after the line:
Dim Dest As Range
When you do this, it doesn't matter what sheet is the active sheet. HTH
Otto

 

[Lillian Lian]

Otto,

Sheet1 has 900 records,
Sheet2 has 1100 records,

if sheet1 of ColumnA(IP Address) match sheet2 of ColumnA(IP Address),

then move all the match from sheet1 of ColumnA, ColumnB, ColumnC, ColumnD
to sheet2 of Column F, ColumnG, ColumnH ColumnI

so sheet2 will have
ColumnA, B, C, D, F, G, H, I

Thanks

Lillian

 

[Otto Moehrbach]

Lillian
Add that little line of code that I gave you in my last posting and it
should work like you want. Let me know. Otto

 

[Lillian Lian]

Otto,

Is this correct? I run this nothing show on sheet2 at all.

Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


Lillian

 

[Lillian Lian]

Otto,

Is this correct? after I run this nothing showing on sheet2 of ColumnF,G,H,I


Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


Thanks for all the help

Lillian

 

[Lillian Lian]

Otto,

Is this correct? after I run this nothing happen?

Sub MoveIP()
Dim RngSht1ColA As Range
Dim RngSht2ColA As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngSht1ColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set RngSht2ColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set Dest = .Range("F2")
End With
For Each i In RngSht1ColA
If Not RngSht2ColA.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
i.Resize(, 4).Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


Lillian

 

[Otto Moehrbach]

Lillian
The code works for me. We must have a disconnect between us. Maybe I
don't understand what you have. If you wish, send me, direct via email,
your file or a sample of it. Fake the data if you wish. I need only the
layout, sheet names, etc. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto