VBA to combine referenced rows into one sheet

Hey there, I am new to this forum and I've got a very specific need that I cannot seem to solve using a search engine or ChatGPT. I am hoping someone here is able to help me out. To provide some context, I work for a child welfare agency and we have to keep track of all the children in care and when they were last seen in their current homes. Every day I need to get responses from the case workers on their efforts. I carry over these responses from one day to the next and either keep the ones that still apply or delete the ones that need new information provided. The easiest way I have found to do this is to create a "combined" sheet which references all of the rows from the other sheets. Then I use a vlookup function to pull yesterday's responses into today's report if the children still appear in the report.

What I have been doing is clicking cell A2 in the combined sheet, typing "=", then selecting all of the rows that contain data in sheet "CAM". Then on the combined sheet I go down to the first blank row and do the same thing for the next worksheet and so on until the combined sheet has referenced all of the rows from all the other sheets (minus the header row obviously). The rows have to reference the original because I need it to update every time someone goes in the worksheet and provides a response to each child on the list.

This process really doesn't take me that long to manually do, but I do it every day and it's just annoying knowing that there has to be a way to include this in my macro. I just cannot figure out how to successfully do it. I have attached a version of the spreadsheet with all of the confidential information replaced. I was going to provide an example sheet to show what the finished product looks like, but it made the file size too big to upload.