List Worksheets & Chartsheets

I have the following code, thanks to Jim Thomlinson, problem is it is only
picking up worksheets, I would like it to also list chartsheets as well.
Help need to change code to do this.

Private Sub Worksheet_Activate()
Dim rngToUpdate As Range
Dim wks As Worksheet

Set rngToUpdate = Range("F5")

Application.ScreenUpdating = False
For Each wks In Worksheets
rngToUpdate = wks.Name
Set rngToUpdate = rngToUpdate.Offset(1, 0)
Next wks
Application.ScreenUpdating = True

Set rngToUpdate = Nothing
End Sub


Thanks, Blue

Blue

Change the

Dim wks As Worksheet

to

Dim wks As Object

and change

For each wks in Worksheets

to

For each wks in Sheets

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Blue" <[email protected]> wrote in message
news:[email protected]...
>I have the following code, thanks to Jim Thomlinson, problem is it is only
>picking up worksheets, I would like it to also list chartsheets as well.
>Help need to change code to do this.
>
> Private Sub Worksheet_Activate()
> Dim rngToUpdate As Range
> Dim wks As Worksheet
>
> Set rngToUpdate = Range("F5")
>
> Application.ScreenUpdating = False
> For Each wks In Worksheets
> rngToUpdate = wks.Name
> Set rngToUpdate = rngToUpdate.Offset(1, 0)
> Next wks
> Application.ScreenUpdating = True
>
> Set rngToUpdate = Nothing
> End Sub
>
>
> Thanks, Blue
>

Thanks Nick, just what I wanted.

Blue


"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> Blue
>
> Change the
>
> Dim wks As Worksheet
>
> to
>
> Dim wks As Object
>
> and change
>
> For each wks in Worksheets
>
> to
>
> For each wks in Sheets
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Blue" <[email protected]> wrote in message
> news:[email protected]...
>>I have the following code, thanks to Jim Thomlinson, problem is it is
>>only picking up worksheets, I would like it to also list chartsheets as
>>well. Help need to change code to do this.
>>
>> Private Sub Worksheet_Activate()
>> Dim rngToUpdate As Range
>> Dim wks As Worksheet
>>
>> Set rngToUpdate = Range("F5")
>>
>> Application.ScreenUpdating = False
>> For Each wks In Worksheets
>> rngToUpdate = wks.Name
>> Set rngToUpdate = rngToUpdate.Offset(1, 0)
>> Next wks
>> Application.ScreenUpdating = True
>>
>> Set rngToUpdate = Nothing
>> End Sub
>>
>>
>> Thanks, Blue
>>
>
>