Simple looping question

[light]

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.

[Jim Thomlinson]

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
>
>

[light]

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?