How do I select and copy ranges of about 5000 rows based on sets of criteria?

I have a spreadsheet into which I have to regularly paste up to 5000 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 providing a sample of spreadsheet that shows approximately how the original one is.

The macro needs to complete the following tasks.


- "currently open" tab: copy the titles (1st row) and all rows with Status "Open" on the tab "All" and move them.

- "xV-currently open" tab: same as what is done for "currently open" tab, but the rows with the Agent "Vesta" needs to be excluded

- "CurOpen - Created prior to 2002" tab: on the worksheet "All", copy the titles (1st row), copy all rows with Status "Open" and created prior to 2002 (column H)

- "xV-CurOpen-Created prior 2002": same as what is done for "CurOpen - Created prior to 2002" tab, but the rows with the Agent "Vesta" needs to be excluded

- "CurOpen - Created in 2002" tab: on the worksheet "All", copy the titles (1st row), copy all rows with Status "Open" and created in 2002 (column H)

- "xV-CurOpen-Created in 2002" tab: same as what is done for "CurOpen - Created in 2002" tab, but the rows with the Agent "Vesta" needs to be excluded

- "Opened and closed in 2002" tab: on the worksheet "All", copy the titles (1st row), copy all rows with Status "closed" and created in 2002 (column H)

- "xV-Opened and closed in 2002" tab: same as what is done for ""Opened and closed in 2002" tab, but the rows with the Agent "Vesta" needs to be excluded

- "Closed in 2002" tab: on the worksheet "All", copy the titles (1st row), copy the rows with closed date (column I) in year 2002.

Let me know if you have any questions.

Thanks for your help.

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Do let me know if you want the destination sheets to be cleared each time you need to run the macro.

Also a check can be put in place if the sheet exists, if it doesnt, then create the sheet.

Another check can be put in place to see if the header exists, if it doesnt, only then copy it.