Create New Worksheets and move data Based on repeating Text Value

[Dawn_B]

I have attached an example of a file that I want to extract data from the original worksheet based on repeating text values and create a new worksheet and copy text to new worksheets. In the attached example I need a macro to create 4 new worksheets based on the repeating Header highlighted in orange. I need a new worksheet for rows 1 to 91, rows 92-194, 195-285, and 286-406.

 

[Larry Haydn]

Are the row numbers always fixed?
If not, is there a way to identify the "sections"?
Does the new worksheets require headings (such as rows 1:3)?

 

[Dawn_B]

Are the row numbers always fixed?
If not, is there a way to identify the "sections"?
Does the new worksheets require headings (such as rows 1:3)?

The row numbers are not fixed. The heading represented in rows 1-2 repeats between for each report. The data in row 3 will change to reflect a report for a new individual.

 

[Larry Haydn]

The row numbers are not fixed. The heading represented in rows 1-2 repeats between for each report. The data in row 3 will change to reflect a report for a new individual.

If you can attach the complete file, it will be easier to help.

 

[Dawn_B]

I'm a new member, and don't see a way to attach the file. I only see the option to upload an image.

 

[Fluff]

You cannot upload files to the site, but you can use the XL2BB add-in to include some sample data in your post.

 

[Larry Haydn]

I do not know whether this site allows me to ask for your file via email.
If that can be done, I can probably provide the codes, which I will post here for everyone's benefit.
@Moderators - is this allowed?

 

[Fluff]

No it is not acceptable to share files privately, everything must remain in the thread. That way everyone can see what is happening & help out.

 

[Larry Haydn]

OK. Thanks for the reply.

 

[Dawn_B]

I cannot use the XL2BB on my work computer. Our IS department won't allow me to download the add-in. I've included a larger image of the file. I basically need a macro that will look for the repeating header that is three rows in length, starting with "Emergency Department Academic Incentive Model ...", and copy and paste the data from where the header begins to the last row before the header repeats to a new worksheet.