Creating a summary worksheet that links to values on other worksheets.

[Getafix1066]

Hi,

I'm trying to create a summary worksheet that allows a column of data from one worksheet to be compared to the same column in a big group of worksheets. I created a macro that did this....

    'Copy column from sheet "F1" to the summary sheet    Sheets("F1").Range("c2:c150").Copy
    Sheets("Summary2").Range("c2:c150").Select
    ActiveSheet.Paste
    
    'Copy column from sheet "F2" to the summary sheet
    Sheets("F2").Range("c2:c150").Copy
    Sheets("Summary2").Range("d2:d150").Select
    ActiveSheet.Paste
    
    'Copy column from sheet "F3" to the summary sheet
    Sheets("F3").Range("c2:c150").Copy
    Sheets("Summary2").Range("e2:e150").Select
    ActiveSheet.Paste

This does do what it says on the tin, but not quite what I intended.

After running this macro, if I look at a particular cell (i.e C66) in my summary, it's contents are "=C60/C59". This is technically correct - it's exactly what's in the cell C66 in sheet F1.

However, what I really want it to say is "='F1'!C66" - so that if the data in cell C66 on sheet ever changes them the corresponding data on the summary page will also change.

How can I do this?

Thanks

 

[Getafix1066]

Ok, I've done a bit more work on this....

This code snippet basically does what I want

   ' Scroll through each of the worksheets that need to be compared    
   For i = minFlight To maxFlight
        'Test if a worksheet for that flight exists
        If worksheetExists(prefix & i) = True Then
            'the worksheet exists, scroll through each of the rows of interest
            For x = minRow To maxRow
                ' Create a link in the summary sheet to the worksheet with the data
                ThisWorkbook.Sheets("Summary2").Cells(x, minColumn + i) = "='" & prefix & i & "'!C" & x   'THIS LINE IS SO SSSSSLLLLLOOOOOWWWWWWW!!!!!
            Next x
        End If
    Next i

The problem is that with a couple of dozen worksheets to compare, and each one have a hundred or more cells that I'm interested in then the line that creates the link between my sumary sheet and the data sheet.....

ThisWorkbook.Sheets("Summary2").Cells(x, minColumn + i) = "='" & prefix & i & "'!C" & x   'THIS LINE IS SO SSSSSLLLLLOOOOOWWWWWWW!!!!!

Is very slow (about 1 second per cell). Is there a way to speed this up?

Thanks