Excel macro parse data from multiple worksheets (with worksheet names)

I've searched around a bit, but haven't been able to find a solution to this exact issue. All the other threads about copying data from multiple worksheets that I was able to find were looking to combine all the data from worksheets into one. However, I have been unable to adapt the codes to my n开发者_如何学JAVAeeds.

I have a workbook with a variable number of worksheets. There are two fixed sheets "errors" and "non-errors," plus a number of other worksheets, the exact quantity of which will vary (as will the names of the sheets). If you are curious, the worksheets are created by another macro as a result of a particular data input, so depending on the nature of the input, the number/names will be different each time the program is run. So the layout of these worksheets is standard across all of them, the data I need are in the same set of cells on each worksheet.

There is a small range of data (containing formulas, but I only need the values that the formulas output, not the formulas themselves) on each of those worksheets that I need to copy onto a new worksheet, under a column whose title header is the name of the worksheet that set of data came from. I am running a Monte Carlo simulation, which is why I need this rather specific set of data.

So, say each of the variable worksheets is set up like this:

(Worksheet name: hotel)

Columns: A      B       C       D
        data   data    DATA    DATA

The data is all numerical. The lowercase data is the stuff I don't need, the uppercase is the stuff I do need.

I want to be able to take the DATA from each of those worksheets, and have it copied to a new worksheet that looks something like this (exact layout isn't important, really, I know enough about Excel macro coding to fine tune the layout, I just don't know how to do this type of parsing):

Columns:   hotel    potato    church  (all worksheet names)
          DATA C    DATA C    DATA C
          DATA D    DATA D    DATA D

DATA C and DATA D are the DATA from columns C and D of the original worksheet, as I explained above.

Anyway, can anyone help me out with this?

I apologize that I don't have any code to provide, but I am literally at a complete loss as to how to complete this. Even if you just point me in the right direction I would be most grateful.

High-level air-code would look something like this
Count the number of data worksheets (nSheets) dimension a variant array:
Dim Ansa() as variant
Redim Ansa(2,nSheets)
Then loop on the data worksheets, get the 2 cells value and put them in the variant array,
Then write the variant array to your new worksheet