How to copy data from 6 dynamic ranges on 6 worksheets into aggregation sheet for analysis

Hello All,

I have lurked here for several years and gleaned a lot of good information and help from many of the posters here.

I finally have a problem I cannot solve despite my searches here and attempts.

Overview:
I have 6 worksheets that are used for data entry. They have differing counts of rows, but the same number of columns. So I have created dynamic ranges which are all working correctly in that they adjust to define the entered data correctly.

I need to summarize the data from the 6 worksheets onto a separate, 7th worksheet if you will. So I end up with hundreds of rows of data that can change depending on what users enter in the first 6 worksheets.

I also need to add data in the aggregated data. Column A needs data added for each row that identifies the worksheet from which the data originated. Call it Region.

For example, on the first worksheet, Southeast Region, I need to copy 256 rows and 44 columns of data. The dynamic range works so in code, I should be able to reference the range. I need to copy that range into my aggregation worksheet called "All Data" and it needs to be pasted in the first available row in column B. Then the string "Southwest Region" needs to be entered on every row in Column A to identify this first set of data as that from "Southeast Region".

Then the code should move down to the first available row and do the same process for the next named region, perhaps "Northeast Region" and then continue through all 6 named ranges that represent regional data.

Many thanks in advance to any who help.

Thank you Thomas (do you go by Tom?)

I have not tried the code yet that you offered as I just created a variation of my actual workbook for posting here. Will start working with the code you offered now.

However, I am not anywhere near as expert in VBA as I would like to be so it is a slow process of trial and error that I go through.

I've attached the workbook. If you look at the AllData worksheet you can see a description of the semi-manual process I am trying to automate fully. You can of course see the code that is triggered by clicking the "Get Data" button. You can also see that my dynamic ranges should be functioning correctly.

One other item to note is that I don't know now how many lines a user will enter on the first worksheet. So in the subsequent worksheets I have entered a formula that links back to the first but places zeros in the cells if there is not a corresponding value in the first worksheet. That is why the dynamic range formula is a bit more complex than perhaps normal.

Any additional suggested code would be a huge help to me on this project.

Thanks to all who may wish to weigh in on this problem. I imaging gathering data from multiple ranges to assemble a large dataset for reporting is not uncommon. Hopefully this will serve as a good example for others.

Best,