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.

Hi Patrick,

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

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

Dan

On Oct 3, 4:51 pm, Patrick wrote:
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.

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.

"dan dungan" wrote:

Hi Patrick,

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

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

Dan

On Oct 3, 4:51 pm, Patrick wrote:
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.

On Thu, 04 Oct 2007 00:00:16 -0000, dan dungan
wrote:

Hi Patrick,

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

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

Dan

On Oct 3, 4:51 pm, Patrick wrote:
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.


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

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

On Thu, 04 Oct 2007 00:53:06 -0000, Chad
wrote:

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

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.