Find and copy across multi-sheet work book
 

Is there a way to look at a cell in sheet1 and then search the entire
workbook for a match, then if a match is found, copy that entire row over the
source row in sheet1.

Find and copy across multi-sheet work book
 

Here is some code to try. It looks a cell A2 in sheet 1 and searches the rest
of the sheets for that value. If it find it then it replaces the row in sheet
1 with the found row. It then goes on to cell A3 (next cell down) in sheet 1
and does the same thing again until it runs into an empty cell in row A of
sheet 1.

Sub FindAndReplace()
Dim wksMain As Worksheet
Dim wksCurrent As Worksheet
Dim rngToFind As Range
Dim rngFound As Range

Set wksMain = Sheets("Sheet1")
Set rngToFind = wksMain.Range("A2")

Do Until rngToFind.Value = Empty
For Each wksCurrent In Worksheets
If wksCurrent.Name < wksMain.Name Then
Set rngFound = wksCurrent.Cells.Find(rngToFind.Value)
If Not rngFound Is Nothing Then
rngFound.EntireRow.Copy rngToFind
Exit For
End If
End If
Next wksCurrent
Set rngToFind = rngToFind.Offset(1, 0)
Loop

End Sub
--
HTH...

Jim Thomlinson


"chris" wrote:

Is there a way to look at a cell in sheet1 and then search the entire
workbook for a match, then if a match is found, copy that entire row over the
source row in sheet1.