J
james8992
I found the following code for a macro that almost does everything
need it to. Right now what the code does is it find a match and paste
the value in the cell right next to it. But rather then just pastin
one value I'd like it to paste the entire row next the the match o
Sheet1.
Example:
Sheet 1: When it finds a match in "Column A" which is Sales Order to
match on Sheet 2 "Column A", it would then paste the row from Sheet
next to the match on Sheet 1
Option Explicit
Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant
With Worksheets("sheet1")
Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With Worksheets("Sheet2")
Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each cell In rng.Cells
If Trim(cell.Value) = "" Then
'skip it
Else
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
End If
Next cell
End Sub
Thanks
Jame
need it to. Right now what the code does is it find a match and paste
the value in the cell right next to it. But rather then just pastin
one value I'd like it to paste the entire row next the the match o
Sheet1.
Example:
Sheet 1: When it finds a match in "Column A" which is Sales Order to
match on Sheet 2 "Column A", it would then paste the row from Sheet
next to the match on Sheet 1
Option Explicit
Sub CopyIDData()
Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim res As Variant
With Worksheets("sheet1")
Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With Worksheets("Sheet2")
Set rng1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each cell In rng.Cells
If Trim(cell.Value) = "" Then
'skip it
Else
res = Application.Match(cell, rng1, 0)
If Not IsError(res) Then
rng1(res, 2).Resize(1, 1).Copy Destination:=cell.Offset(0, 1)
End If
End If
Next cell
End Sub
Thanks
Jame