Moving rows from one sheet to another sheet based on row content

**erickpay** · 06-22-2011, 06:07 PM

Hello,

I have a "Master List" sheet with information organized in columns "Local Government", "Population Range", "Action Type", "Action Category", and "Action". I want to copy rows to a new sheet based on the information in the rows. For instance, if any rows return values of "10,000 - 50,000" for "Population Range" and "Direct" for "Action Type", I want that all those rows showing up in a separate sheet and I want that separate sheet to be updated when rows are changed or added in the "Master List".
I have attached a sample spreadsheet for reference.

**JBeaucaire** · 06-22-2011, 06:46 PM

I created a macro called UpdateReport:

Please Login or Register  to view this content.

Based on whatever criteria you setup in A1:B4 on your "Report" sheet, this macro will find the columns with the data in it, filter the data, then copy over all the matches, if any. Leave the criteria blank that you don't want used in the filter.

To make it easiest to run, I then added a Worksheet_Activate macro into the Report1 sheet tab so all you have to do is go the Master, then back to the Report and it will "reassess" the report for you.

So, just copy the Report1 to Report2 and setup whatever criteria you want for each page at the top of each sheet.

**erickpay** · 06-23-2011, 11:20 AM

JBeaucaire,

This is good. Being new, I am having some difficulty transferring it to my actual workbook (the workbood I posted was a mock-up for personal information security reasons). How do I take the macro and move it to a new workbook which also has more columns? While I'm at asking, is it also possible to have columns from the original workbook dropped on the new sheet. As I type this, I realize that I should probably post a portion of the actual workbook with sensitive info edited...there posted. The columns that I would want removed are highlighted in red.

**JBeaucaire** · 06-24-2011, 03:00 AM

You can access the VBEditor by pressing CTRL-F11 at anytime. The VBAProject should display on the left with the various code modules of interest.

The sheet module REPORT1 has a simple macro in it that calls the UpdateReport macro stored in Module1. If you make copies of the REPORT1 sheet to create other "ready to use" reports with certain criteria at the top, the code in the sheet module will copy, too, so the new sheets will operate the same, as long as you copy the sheet as a whole to make a new sheet. (Right-click the sheet tab and select Move/Copy).

I edited the main macro to copy on the data you wanted.