Copy/Paste Columns from Multiple Worksheets based on Headers into new Worksheet

I've seen a few examples done, but I can't seem to make them work for my scenario.

I have 8 worksheets that I need to take data from 3 columns (based on the column headings since the header names aren't always in the same columns) and paste*special them into a new worksheet.

I have attached an example to help illustrate this.

1) I need the data from Sheets 1 - 8 for the specific column headers: "Full name2", "Office Location", and "Grand Total"
2) Would like to paste the data into "Consolidated" worksheet, which has the 3 column headers defined in that worksheet.
3) The "Grand Total" column is what changes on Sheets 1 -8 making this difficult for me.

Appreciated as always. This is probably something basic for you macro expert, but has been throwing me for a loop.