Hi All

I have a workbook that pulls in datasheets from other workbooks, and renames
the datasheets Datasheet 1, Datasheet 2, etc...

I then use a bit of code to calculate the values of all sheets named
'DataSheet' onto another sheet within the workbook.

My 'calculation' code is below, but I'm wondering if this is the best way to
do it, or whether there is a worksheet formula I should use? (Indirect?)

Thanks in advance.

Trevor Williams

Private Sub CalcAllData()
Dim sh As Worksheet
Range("A1:R57").ClearContents
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "Datasheet*" Then
For x = 1 To 57
For y = 1 To 18
ActiveSheet.Cells(x, y) = ActiveSheet.Cells(x, y).Value +
sh.Cells(x, y).Value
Next
Next
End If
Next
End Sub

Put all your data sheets between two blank sheets, named First and Last, then use this formula in
cell A1 of your activesheet.

=SUM(First:Last!A1)

and copy out to A1:R57.

HTH,
Bernie
MS Excel MVP


"Trevor Williams" wrote in message
...
Hi All

I have a workbook that pulls in datasheets from other workbooks, and renames
the datasheets Datasheet 1, Datasheet 2, etc...

I then use a bit of code to calculate the values of all sheets named
'DataSheet' onto another sheet within the workbook.

My 'calculation' code is below, but I'm wondering if this is the best way to
do it, or whether there is a worksheet formula I should use? (Indirect?)

Thanks in advance.

Trevor Williams

Private Sub CalcAllData()
Dim sh As Worksheet
Range("A1:R57").ClearContents
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "Datasheet*" Then
For x = 1 To 57
For y = 1 To 18
ActiveSheet.Cells(x, y) = ActiveSheet.Cells(x, y).Value +
sh.Cells(x, y).Value
Next
Next
End If
Next
End Sub

Excellent Bernie, thanks very much.

"Bernie Deitrick" wrote:

Put all your data sheets between two blank sheets, named First and Last, then use this formula in
cell A1 of your activesheet.

=SUM(First:Last!A1)

and copy out to A1:R57.

HTH,
Bernie
MS Excel MVP


"Trevor Williams" wrote in message
...
Hi All

I have a workbook that pulls in datasheets from other workbooks, and renames
the datasheets Datasheet 1, Datasheet 2, etc...

I then use a bit of code to calculate the values of all sheets named
'DataSheet' onto another sheet within the workbook.

My 'calculation' code is below, but I'm wondering if this is the best way to
do it, or whether there is a worksheet formula I should use? (Indirect?)

Thanks in advance.

Trevor Williams

Private Sub CalcAllData()
Dim sh As Worksheet
Range("A1:R57").ClearContents
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "Datasheet*" Then
For x = 1 To 57
For y = 1 To 18
ActiveSheet.Cells(x, y) = ActiveSheet.Cells(x, y).Value +
sh.Cells(x, y).Value
Next
Next
End If
Next
End Sub