find, copy and paste

[Patrick]

Hi,

Can someone show me how write a macro that is doing the following;

find a text
select the cells
copy it
paste into another sheet

What is does right now is the following;
find a text
select the cells (A1:B1)
copy it
and past it

But the problem is that the next time the text is located somewhere
else, e.q. A10:B10

So how can I copy this text without setting the cells in the macro?

Reg. Patrick.

 

[dan dungan]

Hi Patrick,

Have a look at Ron De Bruin's web site:

http://www.rondebruin.nl/copy2.htm

Dan

 

[Guest]

A little clarification. Ron's site doesn't show the syntax of the copy
statement. The general form is

OldRange.copy Destination:=NewRange

The word "Destination" is not required, but it makes the code easy for
novices to understand.

 

[Patrick]

Hi Patrick,

Have a look at Ron De Bruin's web site:

http://www.rondebruin.nl/copy2.htm

Dan

I did, but it's not helping me ...

 

[Chad]

Patrick

From your post it looks like you want to find a string and copy the

string and the cell adjacent to this string, so two cells. This can
be modified to suit.

Good luck

Chad

Sub FindIt()
Dim MyString As String

MyString = InputBox("Find what?")
'Find the string in the inputbox
Cells.Find(What:=MyString, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
'Sets the cells copied to 2, pastes the data as well.
ActiveCell.Resize(1, 2).Copy Sheets("sheet3").Range("A1")

End Sub

 

[Patrick]

Patrick

string and the cell adjacent to this string, so two cells. This can
be modified to suit.

Good luck

Chad

Sub FindIt()
Dim MyString As String

MyString = InputBox("Find what?")
'Find the string in the inputbox
Cells.Find(What:=MyString, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
'Sets the cells copied to 2, pastes the data as well.
ActiveCell.Resize(1, 2).Copy Sheets("sheet3").Range("A1")

End Sub

Ok, this works.
Is it also possible to create a function for this one?

So, in the macro, i call this function with 3 values;
The searchstring, sheet and pastecell

e.q. "String", "sheet3", C4

Reg. Patrick.