Need help: Macro or multi-step formula needed in place of a pivot-table method.

edit: it might be the case that it can be solved with a vlookup, but I thought I'd try here first!

I have a small challenge for you all! I work for a museum and am thus tasked with producing Performance figures. The guy before me has set up a system that works well, but is very long-winded, so I'm hoping to simplify it.

The current system: Every month I'm sent learner figures by 12 venues in the county. These are pasted into 'area' spreadsheets (e.g. Newcastle, Sunderland...), which have workbook tabs 'by venue'. There is then a pivot table that works out figures by: Month, Age, 'BVPI' (whether it was a school or not) and 'onsite/offsite'. These final figures are then posted into THREE seperate spreadsheets: Learners by venue (BVPI), All learners (both BVPI & non-BVPI) & non-BVPI. I have noticed in the final figures that age is not mentioned, so I am leaving it out for now.

This takes quite a long time and there is a lot of room for human error.

My solution:

Paste the figures into the 'Area' spreadsheets as normal. Have a VB code or macro that does the following:

1. Only looks at figures for the current month (the date is shown as 2013_2 for February for example).
2. Looks at the BVPI column (which says 'Valid' or '0')
3. If the value is 'valid' then add up the number of visitors in that row (columns D to M, for example)
4. I must also know which figures are 'onsite' and which are 'offisite', which is displayed in column X

It would also be cool to have:
5. Sum of all 'valid' visitor figures for February.
6. From which I (or Excel) can subtract 'valid' figures from 'total' figures to give the 'non-valid' number of visitors (non-BVPI)
(From this, I can have a simple =sum() formula that works out 'total engagements')

Phew. See what I mean? I know what I want to do, but I don't know where to begin. I haven't written VB for almost 10 years; I'm not even sure if it's the right method.

Please advise! I can supply examples of the spreadsheet if needed.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.