Which part of the sheets is to be calculated / generated by the macro? The yellow block? And the user is to indicate the column? Is the macro allowed to put the block in a consistent place (like at least on the same row)? Do you need these yellow blocks at all or can the values be generated on the fly and only the summary table generated?

How many sheets will there be? Do you want to have the macro ask for the column for each sheet or could the user just enter the column letter in a cell (say A1) or mark the column with an 'X' in the first row of the column? (personally I would think the last two options would be preferable over having to select, click OK, select, click OK, etc)