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

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:D 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" wrote in message
...
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,

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" wrote:

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:D 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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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:D 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" wrote in message
...
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,

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" wrote:

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" wrote in message
...
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" wrote:

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:D 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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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:D 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" wrote in
message
...
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