Collate several columns from several sheets to summary page

kryptonian

Board Regular
Joined
Oct 6, 2006
Messages
104
I have a file with a number of sheets: Summary, Raw, and 28-31 sheets more for each day of the month. I want to collate the data from several non-adjacent columns depending on the label on the first row, i.e., columns with the headers FullName, LoggedOnTime, AvailTime, AHT, OCA, and CallsAnswered. The catch here is that these column headers don't necessarily reside in a permanent column, say, FullName can be in A1 on this day then B1 on another.

Here's my plan so far:
1. For each sheet, get the column for FullName, copy it to a Temp sheet, then do it all over again for the other required data so that FullName, LoggedOnTime, AvailTime, AHT, OCA, and CallsAnswered will be in columns A to F in that order.
2. Copy the non-empty range from the Temp sheet and copy it to the Raw sheet.
3. Delete the data from the Temp sheet then do step 1 again for the other sheets.

For step 1, I'm stuck on how to get the macro to copy the column of the needed data.

TIA!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top