Creating a summary table from a large number of worksheets

[Glove303]

Hi,

I have over 100 worksheets that have the same table (same dimensions, in the same location), and I want to create a summary table on another worksheet that sums each cells value.

Looping through the worksheets is far too slow, and I'm sure there is a better solution.

Outside of VBA I could use SUM('WS1:WS2'!R1C1), but I have tried replacing the WS1 with variables and it doesn't work. I'd prefer to do it with VBA anyway if possible, so that I can delete the workheet and create it again, rather than having to leave it there.

Any help would be greatly received.

James.

 

[DanteAmor]

For example:

Sub test()
'
    hini = "sheet1"     'name of your initial sheet.
    hfin = "Sheet3"     'name of your final sheet
    cell1 = "G4"        'cell with result
    cell2 = "A2"        'cell with the values to be added
    With Sheets("summary").Range(cell1)
        .Formula = "=SUM(" & hini & ":" & hfin & "!" & cell2 & ")"
        .Value = .Value
    End With
End Sub