Using copy and paste in macro.....

[Man of Action]

Well here is my problem. I want to copy a value from a worksheet and paste it into find on a different worksheet. I want to find the value on the second worksheet, copy the three cells next to it and change to the first worksheet and paste them into it.

Everything seems to work fine except when I past my value into the find. I want to be able to loop it but when I run it twice it finds the original value all the time instead of the new copied one?!?!?

If anyone can see what is wrong here please let me know it would be greatly appreciated.

 

[Cosmos75]

Try this...


On Error GoTo ErrorHandler
'Insert code

-code-

'The following line will halt the macro before the error handler
'is reached.
Exit Sub
ErrorHandler:
Message = MsgBox("No Match Found")

Edit: If it works, would you mind posting the code here so that I can see if there's anything I can change to make it work better or neater. THANKS!
This message was edited by Cosmos75 on 2002-04-15 13:42

 

[Man of Action]

Could you help me with a loop for about 3000 rows?

 

[Cosmos75]

Sure, I'll try.

First.

What columns are used in Test1.xls and Test2.xls?

How is the data organized?

 

[Man of Action]

Don't worry about it buddy I figured it out.
Thanks for all your help.

Here is the code I ended up using.

Sub Copy_Name()
'
' Copy_Name Macro
'
On Error GoTo Errorhandler
Selection.Copy
FindValue = Selection.Value
Windows("Test2.xls").Activate
Cells.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Main.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1").Select

Exit Sub
Errorhandler:
'Message = MsgBox("No Match Found")
Windows("Main.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

It worked all right. Might make you freak out a bit cuz the screen flips back and forth from two different windows when you loop it. Sorta funny.

 

[Cosmos75]

Glad I could be of help to someone.

Where's the loop part?

You can stop of from blipping (switching between the two by using this at the beginning of your code.

' Turn off screenupdating:
Application.ScreenUpdating = False

and then put this at the end of your code

' Turn off screenupdating:
Application.ScreenUpdating = True

It'll help speed up the code somewhat.
This message was edited by Cosmos75 on 2002-04-15 15:13