Cycle through all sheets; match headers; cut and past all rows from each into 1 master sheet

[yorkbay]

My question is SIMILAR but not exactly the same as another post found on this forum VBA REQUEST: Paste Values of Multiple Sheets Based on the *Header Name* in Destination Sheet

In my workbook the user has an Index Tab where they define policy in columns F:Z
So in F 1 might be Hourly Full Time; G1 Hourly Reduced Time; H1 Salary Full Time - these headers are not static and can be whatever the user needs them to be
Columns A and B are hidden for tracking purposes and versions etc.
Beginning in Column C Row 3 are individual Chapters i.e. C3 = Punch & Schedule Rules; C4=Holiday Rules; C5=Overtime Rules
Where these intersect with F, G, H etc. there is a set of drop down selections (the same for each chapter) so under F1 row 3 would have a set of drop downs of which one is Input Punch & Schedule Rules; F1 row 4 would have the same set of drop downs except the Input option will be Input Holiday Rules; F1 row 4 would have the same set of drop downs except Input option would be Input Overtime Rules.
The user can start anywhere they want from this Index page i.e. they want to start with Salary Full Time and Input Holiday Rules so they select this drop down. The code copies the header from wherever user selected, in this case it finds that C4 is under column H and copies the column H header to a new sheet which is called the Holiday Rules chapter. It copies this header into Column F, presents a punch of questions to the user and when they are done and click save it moves the entire policy over to column G on the Holiday Chapter. All chapters have this 'code' in column F to collect the policy and move the completed policy defined over to the first blank column to the right.
Some headers on the Index won't have chapter input i.e. Salary wouldn't have an overtime chapter filled out so the Overtime chapter headers may only be 2 policy names but Punch & Schedules would have 3 header policy names etc.
Subsequently on a Chapter sheet a user can use an 'edit' button, point to the column that has the policy they need to edit. The code moves it back to F for updates, removes the blank column from which it came, allows the user to save and it pushes to the next blank column in the individual chapter
So, if there are 10 policy names on the Index page and the user has moved about as they want, within each chapter 'headers' won't be in the same place i.e. User could have chosen to fill in Salary for Holliday Rules so Holiday Rules column G will be Salary FT, but then they decided to work on Full Time for Punch & Schedules hence the Punch & Schedules sheet has Salary FT header in column G, and so on.

I now need a 'review' page where every policy from each chapter is pushed to one sheet for review copying each header from each sheet and the subsequent questions and responses from each sheet. For example sheet Punch & Schedules has 120 questions. The code needs to pull all the headers (of which I won't know what they are or what order they are in) and all the questions which reside in column C and the responses to each in columns G:Z. Needs to push this set to a review page. Then circle back to the next chapter, the Holiday Rules which has a total of 44 questions/answers. Match the headers from the new review sheet, cycle to the Holiday sheet, find the header and only bring over the Q and response for each and put it under the matching header below the 120 from Punch & Schedules. Then circle back and do the same for the Overtime sheet etc through all 10 chapters.

Can anyone assist as I've read the thread mentioned at the beginning of this but because I don't have static name, date headers etc. I am at a loss as to how to proceed with finding the matching unknown headers as well as ensuring that all headers and info are picked up since not every chapter (sheet) will have the same set of unknown headers. Any assistance would be much appreciated.

 

[yorkbay]

I know I'm not being clear - sorry totally new to VBA and feel like I got thrown in way over my head Trying to be more clear so someone can please assist.
Sheet 'Index' has a series of column headers beginning in column F.
There are 10 other sheets 'Chapters'
From the Index under any header column, the user can select from a drop down to indicate which chapter they wish to move to to begin inputting data
When they choose 'Input chapter data' the cell updates with the word 'In Progress'.
The code copies the header from Index to Chapter where user inputs data against code in column F
Depending on responses to questions, some questions hide and others show
If they don't know the answer at the time they are inputting data they mark here with 'Revisit' and there is a counter at the bottom of the column F where they are entering data that tracks how many open items they have for each header in the chapter
When they press save in a chapter the saved header with all of this responses moves to the next available column in the individual chapter starting in column G, ALL questions for side by side comparison of headers information and the counter shows the # of items open to 'revisit'. Not all questions will have a response in this view as dependent on response some questions were hidden (not needed).
The order of the headers in chapters may never be the same as the Index due to this flexibility which allows the user to move where they want
The chapters may not contain all headers and in fact may not contain any headers if a chapter is irrelevant to a header or all headers in the Index

All of the above works fine and as a user has moved from chapter to chapter, when they know there are no more 'revisit' responses they come back to the Index and select 'Completed' from the drop down list for that chapter.

I now need a Review Page - 1 pager where an approver can come in and view ALL headers and all of the chapter responses on 1 sheet, mark comments for the original user to correct or, if no comments, sign off approving what they see in this view..
I have set up a review sheet where I've currently copied all chapter questions rows 2 thru approx 450
I now need new code that starts on the Index , copies all the headers to the Review Sheet
Go back to the Index
Look at the first header, check starting with chapter1 to see if it has been marked with either 'in progress' or 'complete'
If so, open the sheet chapter 1, find the header (which can be anywhere in the chapter sheet), copy the response column answers to the appropriate location on the review tab (there won't be a row for row copy \ paste here as now all chapter questions are horizontally on review sheet
Loop back to Index, if the drop down for selection chapter 2 is 'in progress or 'complete', open sheet chapter 2, find the header and copy the response column answers to the appropriate location on the review tab and so on.
I need to continue this process for all headers/chapters to populate the review page
Ultimately I will have a macro button that the user can press at any time to review where they are at with regards to progress since the data will be changing frequently by multiple users of the doc

My brain is just tired from this project so I'm blocked on this piece and hoping this is a bit more clear so that I can find someone who can advise. I've added some mock screen shots here