Copy or Move rows starting with a particular cell value until next blank row

Hi-
I need a macro to loop through the worksheet rows starting at a row with cell value of: "Depth (m)" and ending at the next blank row.

There is text and blank rows before the "Depth (m)" then a series of data then a blank row then more text and what I want is data in between to be copied or moved to a new sheet w/in the workbook.

Here is a sample of the data:
------------------------------------------
Big Lake
27 November, 2012
Jane & Jime
All American City, UT (USA)

OLD meter (#00314)
North
Depth (m) T (deg-C)
0 31.21
1 32.64
2 34.70
3 36.76
4 36.92
5 36.92
6 36.12
7 35.47
8 35.05
9 34.32
10 33.96
11 33.72
12 33.54
13 33.09
14 32.78
15 32.43
16 32.79
17 33.02
18 33.13
19 33.23
20 32.68
21 31.95
22 31.51
23 31.23
24 31.00

Doe (Jane, corrected):
N: 8.0m
S: 8.0m
----------------------------------------------

This is my code so far:
------------------------------

Please Login or Register  to view this content.

----------------------------------------
But I get this error:
Run-time error '1004':
Application-defined or object-defined error
----------------
It works if change this line:
Loop Until .Cells(rownum, 1).Value = 0

to this:
Loop Until .Cells(rownum, 1).Value = "Doe (Jane, corrected):"

I have been picking at this for a while and can't seem to get it to work.
Any advice would be great thank you!
-susan

Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.

Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Here I my file, the rows I'm looking to copy are 8-33. These will vary from sheet to sheet.

Sorry the attached worksheet is missing the data sheet:

Sheets.Add.Name = "data"


Thanks in advance for your assistance!
-s

Hi this was solved in StackOverflow:

Please Login or Register  to view this content.

sborda,

Welcome to the forum.

I have added code tags to your previous post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.