Move Sheet Data to New Sheet, Then Search Through It

[ElHarpie]

We use an Excel workbook to track attendance for workshops that we offer. There are 4 different sheets that contain client data. Each sheet tracks attendance for a different class or workshop, so each sheet asks for different information (column headers are not the same from sheet to sheet).

The workbook is only for 2 months of events at a time. Currently Jan-Feb workshops show up as list options when selecting what the client is attending, then in February we'll delete the client entries and change the list items to workshops for Mar-Apr.

The spreadsheet is attached to this post.

What we would like to do:
Have all client entries for the past 2 months put into a new sheet in the same workbook so that we can search through and find previous clients. It can be complicated behind the scenes (use VBA), but the interface has to be user friendly because we have some older employees.

Would love a way to automate the transition of client data from one sheet to the other, but the discrepancy in each sheet's column headers comes into play here.

After we move the data into the other sheet, what are our options as far as searching goes? Is a pivot table the way to go? We would be searching for names, so a filter could potentially become too much to look through.

[Mumps1]

I think that it would be easier to help and test possible solutions if we could work with your actual file which includes any macros you are currently using. Perhaps you could attach a copy of your file? Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

[ElHarpie]

I couldn't find any way to attach a file when I created this post. I still don't see an option to do so. I guess I could share some other way and post a link here...
The workbook has no macros, only some simple formulas and conditioning rules.
Capture1.PNG
Capture2.PNG

[Mumps1]

To attach a file, click on the "Go Advanced" button at the lower right. Then scroll down a bit and click "Manage Attachments".

[ElHarpie]

Thank you, Mumps1. I have attached the file to the original post.

[Mumps1]

Try the attached file. I've unmerged your merged cells. You should avoid using merged cells because they almost always create problems for macros. I have added the session in cell E1 of each sheet and created a new sheet to match that session name. At the end of each 2 month period, you can change E1 to match the next session and add a new sheet named to match the next session. The macro is a Worksheet_Change event which means that it is triggered automatically when a cell in the worksheet is changed. In your case it will be triggered by a change in any cell in column F (Counsellor) in each sheet. This means that you start entering the data in column A and move to column F making sure that column F is the last column populated. When you do that, as soon as you enter a name in column F and exit the cell, that data will automatically be copied to the new sheet. See if this will work for you and let me know. If you want to see the macro, do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook".

[ElHarpie]

Thank you so much, Mumps1. We opted to go with a filtering search box (found on thespreadsheetguru.com]) for ease of use in searching.
I'll look at tweaking the VBA code you contributed to append new entries to the Client Search sheet based on attendance.

[Mumps1]

Glad it worked out.