Name

[Application.]Sheets([index])

Synopsis

Returns the Worksheet and Chart objects in the active workbook. Sheets is a mixed collection, so you can’t count on every item being a specific type. Instead, you must test check the TypeName before calling methods on the object, as shown by the following code:

Sub TestSheet(  )
    Dim itm As Object, ws As Worksheet, ct As Chart
    For Each itm In Sheets
        Select Case TypeName(itm)
            Case "Worksheet"
                Set ws = itm
                Debug.Print ws.UsedRange.Address
            Case "Chart"
                Set ct = itm
                If ct.HasTitle Then _
                    Debug.Print ct.ChartTitle
            Case Else
                Debug.Print TypeName(itm)
        End Select
    Next
End Sub

Use the Worksheets or Charts method to get those specific object types.

Get Programming Excel with VBA and .NET now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.