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]

Could you post your code? That would help.

[Man of Action]

I guess I should have thought of that. Here is the code to my question:

ActiveCell.Offset(0, -3).Range("A1").Select
Selection.Copy
Windows("Test2.xls").Activate
Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 1).Range("A1:C1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Windows("Test1.xls").Activate
ActiveCell.Offset(0, 3).Range"A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range"A1").Select

[Cosmos75]

On 2002-04-12 10:46, Man of Action wrote:
I guess I should have thought of that. Here is the code to my question:

ActiveCell.Offset(0, -3).Range("A1").Select
Selection.Copy
Windows("Test2.xls").Activate
Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 1).Range("A1:C1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Windows("Test1.xls").Activate
ActiveCell.Offset(0, 3).Range"A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range"A1").Select

Well the part

Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

is always going to look for 714491, which I assume is the value of A1.

You need to use

ActiveCell.Offset(0, -3).Range("A1").Select
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:C1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Windows("Test1.xls").Activate
ActiveCell.Offset(0, 3).Range"A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range"A1").Select

EDIT: What range are you using that containns the values you want to find? I could try to help with the looping part.

Also, I don't understand why you have

ActiveCell.Offset(0, -3).Range("A1").Select

instead of just

Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)).Select

[ This Message was edited by: Cosmos75 on 2002-04-12 12:10 ]

[Jay Petrulis]

Hi,

Rewrite your code so you don't select anything. Is Test1.xls the workbook where you have this procedure, or are you transferring a value from test2 to test1 after you have copied the first part (controlled from a 3rd workbook)?

In any event, I think the following will do what you want...

------------------
Sub test()
Dim FoundCell As String

FoundCell = Workbooks("Test2.xls").Sheets("Sheet1").Cells.Find(What:="714491", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Address(False, False)

ActiveCell.Offset(0, -3).Copy Workbooks("Test2.xls").Sheets("Sheet1").Range(FoundCell).Offset(0, 1).Resize(, 3)

ActiveCell = Workbooks("Test2.xls").Sheets("Sheet1").Range(FoundCell).Value
End Sub
----------------

HTH,
Jay

[Cosmos75]

Jay,

I think Man of Action record a macro by way of selecting a cell, copying the value and then choosing Find and pasting the value in the find inputbox. When you record a macro it enters the value copied but doesn't take into account if the value changes. Whatever value was copied originally when the macro was recorded is the one that will always be used.

[Man of Action]

Thanks for your help guys.

Cosmos,

I tried that change you suggested but when I run the macro the find does not work. It doesn't look for anything?

Confused,
M of A

[Cosmos75]

Kinda odd, it worked for me.

Try opening the VB Editor and running through the macro line by line by using F8. Let me know what's where the error occurs.

[Cosmos75]

My post showed up as

ActiveCell.Offset(0, -1).Range("A1").Select
Windows("Test1.xls").Activate
ActiveCell.Offset(0, 3).Range"A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range"A1").Select

Should be

ActiveCell.Offset(0, -1).Range("A1").Select
Windows("Test1.xls").Activate
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select

[Man of Action]

I got it working. Thanks.

If it can't find the value it just craps out. Is there a way to continue?