Search between worksheets in same file

[Rob]

I am working on a macro and in need of some assistance.

I have a master worksheet, 'SHEET A', which contains
severals rows of data including my unique identifier,
Invoice number. On a separate worksheet, SHEET B' there
is some other data that I would like to move 'SHEET A' if
the invoice number matches. My problem is I don't know
how to take the invoice number from the first row
on 'SHEET A' and search 'SHEET B' until I find that same
invoice number, copy and paste back on 'SHEET A' and then
move to the next row (and invoice number) on 'SHEET A'
and begin the process again.

Any help or advise is greatly appreciated.

Thanks

 

[Rob]

Just a few further thoughts to hopefully explain a little
better.

Each invoice number is only listed once, so they will not
be on multiple rows.

 

[Charles]

Rob,

Afert you find the invoice nr on sheet b what info do you whan t
copy(Range)? And where do you want this info copied to on sheet A?


Charle

 

[Rob]

After I find the invoice number in sheet b, my range to
copy to sheet a would be column a thru column x of the
row in which the invoice number was found and then paste
it beginning at column q on sheet a. Thanks for your
reply Charles.

 

[Charles]

Rob,

Here is what I came up with.

Create a module and past this to it.
You may have to modify it.


Option Explicit

Sub move_data()
Application.ScreenUpdating = False
Dim rng1 As Range
Dim rng2 As Range
Dim inv1
Dim i As Long
Dim a As Long
a = 0
Set rng1 = Worksheets("sheet1").Cells(1, 1).CurrentRegion
Worksheets("sheet1").Activate
For i = 1 To rng1.Rows.Count
inv1 = rng1(i, 1).Text
Worksheets("sheet2").Activate
Cells.Find(What:=inv1, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlWhole).Activate
With Selection
Range("A" & ActiveCell.Row, Range("X" & ActiveCell.Row)).Copy
End With
Worksheets("sheet1").Activate
Set rng2 = Worksheets("sheet1").Cells(1, 17).CurrentRegion
For a = a To rng2.Rows.Count
Next a
If Cells(a - 1, 17).Value = "" Then
Cells(a - 1, 17).PasteSpecial Paste:=xlPasteAll
ElseIf Cells(a, 17).Value = "" Then
Cells(a, 17).PasteSpecial Paste:=xlPasteAll
End If
Next i
End Sub


HTH

Charle