Record Macro to populate a cell in column G with the data found in A5 for each row/repeat

I have several excel workbooks each with at least three worksheets in them that are in report format. Ultimately, I need to extract/merge all of the core data found between the header row and the total row into a new workbook.
To begin I need to include the account number which is within the A5 cell of each worksheet. I can record a macro that extracts just the Account # itself and puts it in column G at the end of the first row of core data. I want to repeat this process for all rows until a blank row is reached. Then I need it to automatically do the same for the remaining sheets in the workbook.

Again, my ultimate goal is to have a "table" of core data to be imported into Access for reporting purposes. I receive it with a report header, several lines with company name and address information, etc. I need only the content line items, the tab names and again, the account number from the cell found in the upper portion of the worksheets.

If there is an easier way to accomplish my overall goal I would very much welcome your input. I can work magic once I have the data in Access. But getting the raw data from so many worksheets is proving difficult.

I get these spreadsheets monthly by the way and will have to run this process monthly to update my table.

Thanks in advance.

I have attached two files - the data in red is what I need to capture to a table.

Also, part of my issue is that the files are not formatted consistently as the data doesn't necessarily begin on the same row in each file.

2013 COMMISSIONS.xlsThis is what I need it to look like afterward.