I have a spreadsheet from A2 to Z1500 and want to find the word "orde
form" which then has a number after it. The word "order form" is in
various locations all over the spreadsheet with different numbers
after each "order form".

I want to loop thru the entire spreadsheet and copy and paste eac
instance of "order form"and its unique number to sheet 2 starting at A
on down.



Thanks in advanc

--
ligh
-----------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...nfo&userid=722
View this thread: http://www.excelforum.com/showthread.php?threadid=39611

I'd just saw your message. Do you still need this. I'm learning myself.

Sub CopyOrderForm()

Dim rngCell As Range 'each cell
Dim rngWork As Range 'working range
Dim lngCopyPos As Long 'copy position row

Set rngWork = Range("A1:Z1500")
lngCopyPos = 2
For Each rngCell In rngWork
If Left(rngCell.Value, 10) = "order form" Then
Sheets("Sheet2").Cells(lngCopyPos, "A") = rngCell.Value
lngCopyPos = lngCopyPos + 1
End If
Next

Set rngWork = Nothing

End Su

--
hidek
-----------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890
View this thread: http://www.excelforum.com/showthread.php?threadid=39611

Thanks hideki

It works fine except I neglected to say that Instead of it
reading left to right by rows,I need it to read top to bottom by
columns.


--
light
------------------------------------------------------------------------
light's Profile: http://www.excelforum.com/member.php...fo&userid=7228
View this thread: http://www.excelforum.com/showthread...hreadid=396118