VBA Code to Create One Summary worksheet of many worksheets in workbook

[brandnew22]

I need to aggregate many worksheets into one final summary sheet. Essentially each "page" of my report will have two of the worksheets side by side for easy comparison. I have the final page mocked up, but I don't know how to go about reproducing this many times without copy and pasting manually, and I will need to refresh with the data from the worksheets going forward.

In short, I need the left "segment" (segment A, Segment C) to be viewed as the main segment, and the segment on the right is compared to the one on the left, so the products for the left are sorted from greatest to least (highlighted in yellow) and the segment to the right needs to correspond to the order. The original data extracts are not sorted.

I have branstormed with using Indirect to look up each "segment" tab to pull the data in, but then you run into the issue that one segment might only have regions A, C and E, and another might have A,B,C,D and F. There isn't an easy way to do a lookup to quickly reproduce the report.

A mockup is attached.