Macro to show data from multiple worksheets on one worksheet based on specific criteria

[mlucey01]

I have a workbook with a tab for each month of the year in it. Each tab contains rows of questions generated from the weekly meetings from each month. One of the columns is a "Responder" column, which is populated with the name of the person who is responsible to answer the questions or investigate the issue raised. I would like a master sheet that we can go to and enter a responder name into cell A1 and the sheet would be populated with the rows from each monthly worksheet where that name was assigned as the "responder". That way anyone is a responder can go to on one sheet to see the items they are responsible for, rather than paging thorough each monthly sheet to find their assignments. To be clear, each row will contain multiple columns that contain information such as the question asked, the date it was asked, an index number, the person who asked the question, the assigned responder and the responders answer. the method of response, finally the date of the response. This would basically as a summary sheet for the assigned responder.

I am not a beginner, but I am also not an expert in Excel. So any and all help anyone can provide would be greatly appreciated.

[oeldere]

It depends on the lay out of your data on the sheets.

Maybe you can pull all data of all sheets to 1 worksheet, and after that use a pivottable for that.

[mlucey01]

Thanks oeldere. Do you mean that I just be adding all of the data to one sheet when entered, or is there a way to pull all the data from the 12 sheets to a master sheet?

[oeldere]

If the lay out of all the sheets are the same, we can pull all data (of all the 12 worksheets) to 1 mastersheet.

It can be done with VBA.

After that you can use the mastersheet to analyze the data (e.g. with an pivot table).

If you want this solution, you have to add an excel example without confidential information.