Loop through worksheets

[A.for.Andy]

Hi All

When a workbook opens, I am trying to loop through all 26 worksheets,
adding up the numbers in range A1 of each sheet and displaying the
result in a message box. I am failing miserably! One attempt shown
below. Please let me know where I'm going wrong!

Private Sub Workbook_Open()

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
total = 0
For I = 1 To WS_Count

ActiveWorkbook.Worksheets(I).Range("A1").Select
nexttotal = Worksheet(I).Range("A1").Value
total = total + nexttotal

'MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

MsgBox total

End Sub

Many thanks in advance!

Andy

 

[Dave Peterson]

Sometimes, you call your accumulator Total. Sometimes, you call it nexttotal.

I bet that's enough...

But you don't need to select the worksheet:

Private Sub Workbook_Open()

Dim WS_Count As Long
Dim I As long
dim Total as double

WS_Count = me.Worksheets.Count

For I = 1 To WS_Count
if isnumeric(me.worksheets(I).range("A1").value) then
total = total+ me.worksheets(i).range("a1").value
end if
next i

MsgBox total

End Sub

 

[RyanH]

This will do it for you. Nice and simple.

Sub AddRanges()

Dim sh As Worksheet
Dim i As Double

For Each sh In Worksheets
i = i + sh.Range("A1").Value
Next sh

MsgBox "Total = " & i

End Sub

Hope it helps!

 

[A.for.Andy]

Thank you both kindly gentlemen...both work a charm! Greatly
appreciated!