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

Try:

Sheet.Name

Rather than:

ActiveSheet.Name

Regards

Trevor


"Dan Winterton" wrote in message
...
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

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

"Dan Winterton" schreef in bericht
...
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