Eurekaonide
Active Member
- Joined
- Feb 1, 2010
- Messages
- 422
Can anyone tell me why my code below is erroring stating its got an Else without and IF please?
And why is it not looping through all my worksheets, it only puts the formula in on the first active worksheet ? Thanks ever so
And why is it not looping through all my worksheets, it only puts the formula in on the first active worksheet ? Thanks ever so
VBA Code:
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
With Ws
If Range("O7").Value = "51" Then Ws.Range("AE2").Select
'With Selection.Interior
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
'.ThemeColor = xlThemeColorAccent6
'.TintAndShade = 0.799981688894314 ' green
' .PatternTintAndShade = 0
'End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
Range("AE4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AE4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
Application.CutCopyMode = False
ElseIf Range("O7").Value = "52" Then Ws.Range("AF2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 ' green
.PatternTintAndShade = 0
End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
Range("AF4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AF4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
Application.CutCopyMode = False
Else: Range("O7").Value = "53a"
Ws.Range("AG2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314 ' green
.PatternTintAndShade = 0
End With
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
Range("AG4").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Range("AG4").Select
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
Application.CutCopyMode = False
End If
End With
Next Ws
MsgBox "Done"
End Sub