Why won't this loop?

[Steph]

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??

 

[Ron de Bruin]

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

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

 

[Bob Phillips]

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)