Macro to find, copy and paste
 

Hi,
I have two excel sheet. I am trying to copy a name from list of names in
sheet1, find it in sheet2 - copy the entire column (which contains the name
)and paste it into another column in sheet2. The macro works perfectly fine
until I hit a name that is not is in sheet2. When I put' On error goto....'it
works fine if it is after the Exit sub. It does not work otherwise.
This is what I want -When it hits a name that is not in sheet2, I want it to
go to the next name in sheet1 and continue with the process.

Any help would be appreciated!


Dim x As Range
Sheets("Sheet1").Select
Set x = Range("A1")
Do
x.Select
If ActiveCell.Value = "" Then
Exit Sub
End If
Application.CutCopyMode = False
Selection.Copy
Sheets("Req and Status").Select
Rows("1:1").Select
'On Error GoTo message

Selection.Find(What:=x, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Sheets("Sheet1").Select
Set x = x.Offset(1)

Loop

End Sub

Macro to find, copy and paste
 

Your approach needs much work but you would be better served using FINDNEXT.
Look in vba help index for a good example.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SeekAns" wrote in message
...
Hi,
I have two excel sheet. I am trying to copy a name from list of names in
sheet1, find it in sheet2 - copy the entire column (which contains the
name
)and paste it into another column in sheet2. The macro works perfectly
fine
until I hit a name that is not is in sheet2. When I put' On error
goto....'it
works fine if it is after the Exit sub. It does not work otherwise.
This is what I want -When it hits a name that is not in sheet2, I want it
to
go to the next name in sheet1 and continue with the process.

Any help would be appreciated!


Dim x As Range
Sheets("Sheet1").Select
Set x = Range("A1")
Do
x.Select
If ActiveCell.Value = "" Then
Exit Sub
End If
Application.CutCopyMode = False
Selection.Copy
Sheets("Req and Status").Select
Rows("1:1").Select
'On Error GoTo message

Selection.Find(What:=x, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Sheets("Sheet1").Select
Set x = x.Offset(1)

Loop

End Sub