Macro that creates new column and creates pivot with filter based on new column

Problem: I have an extract from our accounting system that I want to do three things with:

1) Insert a column titled "Project Number" and have that column look at the right-most four characters of the column that precedes it (one column to the left) - simple task, except for the number of rows is dynamic (weekly extract), and I need help with the function that looks at the preceding column to determine the number of rows to autofill.

2) Create a pivot based on the table - simple task

3) Create a filter based on four "Project Number" fields that may or may not exist in the table.

Simply stated, I want to create a new column, titled "Project Number", pivot the table, and filter the pivot based on "Project Number" even if the project number does not exist in the current table. I am trying to report spending based on four projects that our COO is paying very close attention to.

Expected outputs would be a pivot that filters based on the four project numbers and displays it in an orderly (sorted) fashion.

I can get as far as filtering on the project number, but macro errors out if the project I am looking for is not in the data table to begin with.

Let me know if you need clarification,

BW

Please see attachedTestFile - Copy.xlsx

The Green column on sheet 1 is the column I would add, then pivot on all the data, with project number the key. I would like to filter automatically the project numbers listed, even if they are not present in the 'parent' data. That is, the file changes weekly, and sometimes all four projects are not present. When this is the case, the current macro I have (a simple recorded macro) errors out. Also, I would like the formatting to be 'comma.'

Thank you for your help!

BW

Rory,

I noticed that your macro, though it produces the exact results, runs much, much faster. Is this because of the screen updating (essentialy your code does not show changes on screen, it just implements them)? Both were very helpful, and have been added to your reputation.

Thanks!

BW

This works like a charm! I like to see the screen updating, though not necessary. Thank you so much! Your reputation has been added to.

thanks,

BW