Using copy and paste in macro.....

Man of Action

New Member
Joined
Apr 11, 2002
Messages
15
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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top