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

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

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

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

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

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