Loop Through all worksheet

[Excelnoobisme]

Hi,

Need some help for code to loop through all tabs in a workbook.
I.e. i have 4 tabs namely, AA, BB, CC, DD.

i wish to run a macro where it will sum column B and return the value on cell C1 on all the 4 tabs.

Thanks

 

[dave3009]

Try something like

Dim sht As Worksheet
For Each sht In Sheets(Array("AA", "BB", "CC", "DD"))
    sht.Range("C1") = WorksheetFunction.Sum(sht.Range("B:B"))
Next sht

 

[Excelnoobisme]

What happen if i have 100 tabs? Do i have to type in 100 different tabs name?

 

[Rasm]

Try something like this

Public Sub SumInAllSheets()
    Dim MyRange As Range
    For i = 1 To Sheets.Count
        With Sheets(i).UsedRange
            LastRow = .Rows(.Rows.Count).Row
        End With
        Set MyRange = Sheets(i).Range("B1:B" & LastRow)
        Sheets(i).Range("C1") = Application.WorksheetFunction.Sum(MyRange)
    Next i
End Sub