I have a problem I haven't specifically been able to solve using any search functions.

I have multiple sheets (individual product forecasts) I need to consolidate for a summary file ("Consolidated" tab in the workbook). I will leave the consolidated tab immediately before the first working forecast tab. I would like to trigger it with a button rather than on a sheet activate event for the "Consolidated" tab or something. When it fires I need the data after the header on the consolidation to be copied to the "Last Consolidation" then cleared from the current consolidation tab.

I then need to start with the tab next to the consolidation tab (first working forecast), and work through each sheet until it hits a sheet called "STOP", then stop as there are budgetary sheets after that unless there is a better way to cycle through the worksheets and stop at a specific sheet I can define. I need to select entire rows from each sheets if there is a value in column A and paste the data to the Consolidated tab at the bottom. It's predicated on people numbering their forecast adjustments, but I am OK with that.

There is an example attached. There are three analysts and over 80 product tabs spread across their three individual forecast tabs so being able to run this is huge. We are currently consolidating them manually (biweekly). There is a shortened example file attached.

I am really struggling with this. Help on any portion of this is appreciated. It seems like a tall order, but I'm sure not for some on here.

Test FIle.xlsx