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

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

This should be close to what you want

Sub FindOrder()
Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range
Dim rngCurrent As Range
Dim rngFirst As Range

Set wksCopy = Sheets("Sheet1")
Set wksPaste = Sheets("Sheet2")
Set rngCopy = wksCopy.Range("A1:Z1500")
Set rngPaste = wksPaste.Range("A2")

Set rngCurrent = rngCopy.Find("Order Form")
If rngCurrent Is Nothing Then
MsgBox "No Order Forms Found"
Else
Set rngFirst = rngCurrent
Do
rngCurrent.Copy rngPaste
rngCurrent.Offset(0, 1).Copy rngPaste.Offset(0, 1)
Set rngPaste = rngPaste.Offset(1, 0)
Set rngCurrent = rngCopy.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
End If
End Sub

--
HTH...

Jim Thomlinson


"light" wrote:


I have a spreadsheet from A2 to Z1500 and want to find the word "order
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 each
instance of "order form"and its unique number to sheet 2 starting at A2
on down.


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

Jim

Thank-You very much. I notice the macro is reading accross by rows. I
would like it to read down by columns. Tried changing the offset but
then it has trouble with blank cells. How do I do this adjustment?


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