Advance apologies on possibly breaking rule #9 - I'm risking over-explanation because I'm not sure I have the jargon to keep it short and still communicate what I'm looking to do... 
I have inherited a huge mess of Excel files (probably about 20-30 or so), unfortunately all required. What's NOT required are the dozen or so files that end up being created manually and temporarily to enable us to create the ones that ARE required... I'm trying to streamline an existing corporate process as much as possible, and while I can see how it needs to work, I'm not too bright at seeing how to make it so... 
Here's my problem:
I am working with multiple corporate sites, with several possible files for each site (could be just 1, could be 3), each week. Each weekly site file is based on that site's information from one master file (which contains ALL of the data). What has been happening is that we take this one master file and manually (via sorts, filters, and pivot tables) extract each site's individual info - then, once we have all the info for a site, we refine it further based on what DIVISION or REGION it represents, and then further what TYPE of record it is - a handful of individual Excel files are created based on this. These are then mailed to the sites. As is, this process is taking us 2-3 days each week to get done.
I've built a little template to help me visualize what I'm needing to do (it's attached). Basically, I'm needing to paste the relevant information into the RAW DATA sheet - then be able to manipulate that data so that each site's information gets placed onto the correct tab for that location/information. For example, the raw data might indicate 'Edmonton' as a SITE, with 'Sale' or 'Transfer' as a SERVICES NAME, and 'West' or 'Chicago' as a DIVISION or MARKET. (There is a lot more information across the row, and I'd need to move the whole row over to the site sheet, but those are the only categories I'd need to filter by.)
To simplify (hopefully):
I need to pull out all records relating to each particular site (we'll stick with Edmonton for this example) from the RAW DATA sheet, column I.
- First, anything with a DIVISION of 'West' (in column O) would need to be moved to a sheet called EDM WEST
- then, anything with a MARKET of 'Chicago' (in column P) would need to be moved to a sheet called EDM CHIC
- then, anything with a SERVICES NAME of 'Xfer To Local Sales' (in column U) would need to be moved to a sheet called EDM XFER
- finally, everything else that's for site Edmonton will be moved to a sheet called EDM.
I'm needing that process to repeat for each site, unfortunately, of which there are about 10.
I'd like to understand how to automate this? I'm quite good with straight-forward pivot tables, pretty comfortable with minor macros, but cannot figure out how to run a VLOOKUP to save my life. Any suggestions which make use of macros/buttons will be REALLY awesome, since I'm not certain how Excel-savvy all users of this will be. My unglorious solution is to use the Macro Recorder to step through each of these steps manually, for each site, creating a button for each site's actions, and just clicking the buttons in the order above to get the desired results. I'm wondering if there's anything that's a bit less clunky than that? Any help is SUPER APPRECIATED!
