Hi Guys
Im creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet.
Ive attached my example.
Thank you very much for any input
Re: Calculating Data From Each Sheet In A Workbook
Try this exelent code... It works beautiful!! Even if you add more sheets...
Dim point() As Double
Dim wsSheet As Worksheet
Dim wsRange As Range
ReDim point(1 To 5)
point(1) = 0
With Worksheets("Totals")
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Name <> "Totals" Then
For Each wsRange In wsSheet.Range("B12", "B32")
If wsRange.Value = .Range("A20").Value Then
point(1) = point(1) + wsRange.Offset(0, 1).Value
point(2) = point(2) + wsRange.Offset(0, 2).Value
point(3) = point(3) + wsRange.Offset(0, 3).Value
point(4) = point(4) + wsRange.Offset(0, 4).Value
point(5) = point(5) + wsRange.Offset(0, 5).Value
End If
Next wsRange
End If
Next wsSheet
.Range("C12") = point(1)
.Range("D12") = point(2)
.Range("E12") = point(3)
.Range("F12") = point(4)
.Range("G12") = point(5)
End WithRe: Calculating Data From Each Sheet In A Workbook
cheers buddy
ill get a look at that just now
Re: Calculating Data From Each Sheet In A Workbook
It is VERY bad Spreadsheet design to spread data over umpteen different Worksheets. See Efficient Spreadsheet Designs.
However, I know you wont change so use Excel's Consolidation feature.
Don’t have an account yet? Register yourself now and be a part of our community!