Why won't this loop?
 

Hello. I have the following code:

Sub CostCenter()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name < "Consolidate" Then
Set frng = Range("B8:B125")
With frng
.Formula = "=$C$2"
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

It enters the formula on 1 sheets only, and does not loop through all
worksheets within the workbook except "Consolidate". What am I doing
wrong??

Why won't this loop?
 

Change Set frng = Range("B8:B125") to

Set frng = ws.Range("B8:B125")


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steph" wrote in message om...
Hello. I have the following code:

Sub CostCenter()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name < "Consolidate" Then
Set frng = Range("B8:B125")
With frng
.Formula = "=$C$2"
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

It enters the formula on 1 sheets only, and does not loop through all
worksheets within the workbook except "Consolidate". What am I doing
wrong??

Why won't this loop?
 

Do you want to process all sheets or just Consolidate? The For Each suggests
all, the If ws.Name = "Consolidate" suggests just one.


Is this what you want

Sub CostCenter()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Worksheets
Set frng = Range("B8:B125")
With frng
.Formula = "=$C$2"
End With
Next ws
Application.ScreenUpdating = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
om...
Hello. I have the following code:

Sub CostCenter()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name < "Consolidate" Then
Set frng = Range("B8:B125")
With frng
.Formula = "=$C$2"
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub

It enters the formula on 1 sheets only, and does not loop through all
worksheets within the workbook except "Consolidate". What am I doing
wrong??