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.

thank you stnkynts...i'm a lot closer than i was before.

when i run the macro, it pastes all the data except for "Full name2" and "Office Location" for the last 3 worksheets. In my real workbook, I have these worksheets named something other than Sheet1, Sheet2, etc. I was thinking if there was a way to name which worksheet I want to copy from to avoid any confusion? Otherwise, this macro worked in the test example.

did a little editing and got it...thanks stnkynts for your help!