vba to calculate correlations from averages based on header criteria

[cjcass]

Hi,
Please have a look at the pic below and it's hopefully self-explanatory to a degree.
I'm looking for a macro I can run that will go to all the wkshts (tab names) in Col W in the wksht 'Results' and work out Correlations based on the Header Criteria in Cols AA and AB. So using the example the first part of the calculation is to work out the average across Row4 for the yellow criteria (6.5), and then the average for the green (4.0), then do the same the whole way down the table and give the Correlation for all the averages in each wksht. In this example for the wksht '1' the result is 0.43. The tables in each wksht cover the range A3:GR50000 but not every table will be that size, most will be <10000 rows down (at the moment).
Can this be achieved?
Any help much appreciated
PS. I've assumed a solution using cell formulas is probably not an option.

 

[cjcass]

Hi offthelip (if you're around).
I've just noticed a slight issue with the correlation code which you developed for me on this thread and wondered if you could assist a bit further.
If there is no data in any of the worksheets 1-50 then the correlation formula returned for all the wkshts with no data is as follows: =CORREL(GS1:GS4,GT1:GT4) and given that this formula is located in cell GS2 it results in lots of circular reference errors.
This is a bit strange given that I can clearly see that your code instructs: Formula = "=CORREL(GS4:GS" & lastRow & ",GT4:GT" & lastRow & ")"
Any help you can further offer would be much appreciated.

 

[cjcass]

ok, have found a workaround which is not very advanced from a VBA prespective but it converts all the CORREL formulas (incl. the ones with circular references) to values.
My code is below, it looks a bit chunky so if anyone knows of a way to make it slicker please let me know as I like to learn...

Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", _
        "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", _
        "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50")).Select
    Sheets("1").Activate
    Range("GS2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheet93.Select
    Range("AI73").Select