Dear Tom

It is working very well. :)
there was 1 space at the end of the range (BUSH )and I didn't conside
that.

Thank you very much.

your help removed tow hold point in my project.
May you be so kind to remove the last one.
In the following I don't want to copy entire row but just from column
to N.

If LCase(sh.Name) < "sheet1" Then
Set rng = sh.Cells.Find(what:="BUSH ", lookat:=xlWhole)
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:= _
CurrentWkbk.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2)

Yours
HM

--
hm
-----------------------------------------------------------------------
hme's Profile: http://www.excelforum.com/member.php...fo&userid=1593
View this thread: http://www.excelforum.com/showthread.php?threadid=27697

Maybe this will help:

If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:= _
CurrentWkbk.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2)
end if

If Not rng Is Nothing Then
sh.cells(rng.row,1).resize(1,14).Copy Destination:= _
CurrentWkbk.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2)
end if



hme wrote:

Dear Tom

It is working very well. :)
there was 1 space at the end of the range (BUSH )and I didn't consider
that.

Thank you very much.

your help removed tow hold point in my project.
May you be so kind to remove the last one.
In the following I don't want to copy entire row but just from column A
to N.

If LCase(sh.Name) < "sheet1" Then
Set rng = sh.Cells.Find(what:="BUSH ", lookat:=xlWhole)
If Not rng Is Nothing Then
rng.EntireRow.Copy Destination:= _
CurrentWkbk.Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2)

Yours
HME

--
hme
------------------------------------------------------------------------
hme's Profile: http://www.excelforum.com/member.php...o&userid=15930
View this thread: http://www.excelforum.com/showthread...hreadid=276976

--

Dave Peterson