For each... next (to act on each worksheet)

[Guest]

I want my VB application to go through each sheet, check the value in a cell,
and re-name the worksheet according to the value in the cell.

I tried using a "For Each... Next" loop, but VB didn't like using this with
"Sheet" and "ActiveWorkbook" objects.

For Each Sheet In ActiveWorkbook
Select Case Range("A3").Value
Case "aaaa": ActiveSheet.Name = "Company A"
Case "bbbb": ActiveSheet.Name = "Company B"
End Select
Next Sheet

How can I make this work, or structure another loop to do the same action?
Any help is greatly appreciated.
Rgds,
Dan Winterton

 

[Trevor Shuttleworth]

Try:

Sheet.Name

Rather than:

ActiveSheet.Name

Regards

Trevor

 

[F.H. van Zelm]

Hi Dan,

Could it be:

Dim mySheet As Worksheet
For Each mySheet In ActiveWorkbook.Worksheets
Select Case Range("A3").Value
Case "aaaa"
mySheet.Name = "Company A"
Case "bbbb"
mySheet.Name = "Company B"
End Select
Next

I tried Trevors approach ... no go.

Frans