How do I select and copy ranges of indefinite numbers of rows based on sets of criteria?

Hi

I'm new to this forum so please advise if this request is not presented in an acceptable manner. I'm currently trying to teach myself VBA but I can't find a solution to what I think should be a really easy problem. Basically, I want to select a range of rows, using criteria in one column then copy and paste the rows containing each criteria into different tabs. Number of rows for each criteria varies with each data input. Problem follows :

I have a spreadsheet into which I have to regularly paste up to 4000 rows of varying data. The column headings are always the same and the Criteria I'm sorting by is always the same. I am trying to write a macro to sort the data by the criteria, then copy and paste each block into a separate tab. I am stuck and wonder if anyone can help me.

I have written code to sort the data (which works) but I can't seem select the blocks I need. If anyone could give me some simple code to make the attached small example work, I could apply this to my actual data.

The attached spreadsheet has 5 columns and 18 rows.
Row 1 is the heading.
Column E contains the criteria which is already sorted with Criteria 222 in Rows 2-6, Criteria 333 in Rows 8-11, Criteria 444 in Rows 12-14, etc.

The number of rows containing each group of Criteria will change with each new input of data.

I'm trying to write a macro that will do the following:
(1) detect the rows containing the text "Criteria 222" in Column E,
(2) highlight all those rows, and copy and paste them into Sheet 2 of the workbook.
(3) Then I want to detect the next group of rows, ie, those with the text "Criteria 333" in Column E,
(4) highlight all these rows and copy and paste them into Sheet 3,
(5) "Criteria 444" in to Sheet 4, etc, etc, all the way to the bottom of the data.

I realise there are a many less cumbersome ways to carry out this task but I particularly want to do it step by step as above. As I'm new to VBA, anything more complicated will just confuse me and I won't understand the code. Also, if I can do this, I can apply it to other similar projects where I need to select and work with ranges based on criteria.

Hi Patricia,
try it

Please Login or Register  to view this content.

Try the attached.

My, that was quick! Thank you both so much. nilem, your code is amazing and for speed to solve my immediate problem certainly works but I'll have to try and understand some of the code and work it out as it's a little advanced for me at the moment, still, I'm anxious to learn. AB33, I'm pretty sure I can work this one out and apply it quickly to other projects as well as my to current dilema. Very grateful.