Find and instance of "X" and all rows between the next instance of "X"

[Pilotflyby]

Column A contains the text "Root" and then rows of data before the next
"Root" in column A. I need to find the first instance of "Root" copy that
row and all other rows below it until the next "Root" is found. These copied
rows need to go into a new worksheet and have the worksheet renamed to the
same thing as found in Column C of the row that "Root" came from. The range
that "Root" can be found is A2:A5000

Example:
A
1
2
3
4 Root
5
6
7
8
9
10
11
12
13 Root
14
15
16
17 Root

In the above example, I need
Rows A4 - A12 copied to a new worksheet and the worksheet name changed to the
content of C4
-Then-
Row A13 - A16 copied to new worksheet and worksheet name changed to content
of C13
Loop until all of the data in the master worksheet called "Monday Project
Report" has been accounted for.

Note:
Row 1 contains column titles
Columns used = A-J

Thank you in advance for any help provided. I searched for quite some time
on this form and was suprised I could not find anything similar to this
request that I could modify.

Regards,

Mark.

 

[MSweetG222]

Pilotflyby,

Consider the following...
1. In Col K, title it "Related Records" and insert the following formula
=IF(RIGHT(A2,4)="Root",A2,K3) for all record rows. I think you will get the
general idea of the formula. Modify it as needed to fit your case.
This will give your records a key that ties them together.
Copy & paste col K as values.
2. determine all the unique keys in the Related Records column
3. create a loop using the Related Records column
4. Autofilter the Related Records column for the 1st value (ex. 4 Root)
5. Copy & paste visible cells to desired location
6. Delete Column K
7. Save w/b or any other steps you need to perform
8. Continue until completed

The key is to create a relationship between the various rows. Hope this
gives you a starting point.

 

[Pilotflyby via OfficeKB.com]

Thank you MSweetG222 for your suggestion. I will work with your idea this
weekend and see what happens.

-Mark.

Pilotflyby,

Consider the following...
1. In Col K, title it "Related Records" and insert the following formula
=IF(RIGHT(A2,4)="Root",A2,K3) for all record rows. I think you will get the
general idea of the formula. Modify it as needed to fit your case.
This will give your records a key that ties them together.
Copy & paste col K as values.
2. determine all the unique keys in the Related Records column
3. create a loop using the Related Records column
4. Autofilter the Related Records column for the 1st value (ex. 4 Root)
5. Copy & paste visible cells to desired location
6. Delete Column K
7. Save w/b or any other steps you need to perform
8. Continue until completed

The key is to create a relationship between the various rows. Hope this
gives you a starting point.

Column A contains the text "Root" and then rows of data before the next
"Root" in column A. I need to find the first instance of "Root" copy that

[quoted text clipped - 43 lines]

Mark.