Macro for using an advanced filter RRS feed

  • Question

  • Hello,

    Could someone provide a macro for doing the following tasks? This is manually done every week and works fine but it takes time to do it all.


    There are two workbooks A and B sent to us each week where each workbook has a worksheet named Resource View. There are a different number of rows in Resource view every week where some have less or more than the previous week. Each workbook is sent to a different area and other than the resource view the other worksheets are different.

    In Workbook B we delete all data in Resource view. We copy Workbook A's Resource view into Workbook B's Resource view.  Workbook B has a worksheet called Instructions that has a criteria range Instructions!$R$1:$V$6 for an advanced filter that we use as the Criteria range in WorkbookB worksheet Resource view. The criteria range never changes but we have to type it in every time in that Workbook B worksheet Resource view. We could put the criteria range in the WorkbookB worksheet resource view in that same spot R through V since the data coming in will always only go to column Q.

    The List Range we use varies but it is usually 4 to 5 consecutive weeks and would be as an example i:L.

    The criteria range is really >0 using an or condition. When we built that criteria range we had to be careful to do a delete in every blank spot in that criteria range of row 1 through 6 R through V. Otherwise, it wouldn't filter correctly.

    After all this we run a macro that groups the rows of data.


    Friday, April 10, 2020 8:13 PM