Simple transfer of information??! So lost.

Many possibilities, all take some work.
1) Just use one master table with all of the data. Then use data filters to switch between 'all', 'Colonial', 'Independence', and 'Gotham'. Create a table by highlighting your data and the hitting Ctrl-T.
2) Create a pivot table of your master table using the offices as one of your filters. If you really want them on separate sheets, then you can create 3 pivot tables, one for each office.

on the master sheet, do you want to show a total for all other sheets, or separate totals for each sheet?

I'm still thinking a table would be your best and easiest solution, but, of course, bosses can ask for items that make the solution more complicated.

my boss does want different tables to email the invoices each month

What does this mean? Do you copy/paste the tables into the email? Do you want to send an excel file with just the sheet of the location (e.g. 'Gotham') with the others deleted (note: this would cause problems if your data source is the master sheet)? Or, do you just send the entire spreadsheet to the locations and they click on the tab that is appropriate for them?

If it is copy/paste, then a table should work. If it is you send the entire spreadsheet, then a table should work (performing a filter is not much of a step beyond clicking the appropriate sheet). And, like I said, sending individualized spreadsheets for each geo may cause problems based upon how the 'master' sheet is created.

Additionally we will be adding new markets

Actually, that statement adds more benefit to a single table solution. You just have a column for 'Location' and when you want to add a new one, then you just type in the new name. Using macros and/or separate sheets will be more complicated if you want to add locations in the future.

I tried the filters and it won't let me choose the sheet I'd like

I'm not sure what this means.

Finally, what you really want, I think, is a database. If your office suite included Access, then that is the tool to use. It can generate reports for each location based upon a master database with ease. It takes some education, but it sounds like you are learning Excel as well, so if you are going to learn a new tool, it may as well be the correct one.

if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you coujust enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though

Okay, humor me for just a minute. Look at the modified spreadsheet where I created a table.

To see the different markets, just click on the drop down arrow next to the 'Market' header and choose the market(s) you want to see. Also notice how the Total line at the bottom adjusts to show just the total of the un-filtered list.

The table also allows you to do other filters such as looking for just a single client or for orders >$1000.

I understand the boss wants separate worksheets, but that seems like a lot of work (on your part) when all he has to do is click on an arrow and select the market he wants to see (not much more than clicking on a sheet).

do you have a version you could upload to let us take a look at what you have? (layout etc)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.