VBA: Dynamic List from same column on multiple sheets

Update:
In the end this did not help me. The number of sheets I need to reference along with the amount of rows brings the character length too high for excel to complete the formula. After the 2 sheet the calculation stops.

Looking for an alternative now.
Gray.

Added another Excel to this to show that it doesn't work after the 32,767 characters is reached
Just take Tab 6 and slide it after END and back