Looping through all worksheets

[shebe228]

My code is supposed to loop through all worksheets and perform the formula and sorts on each worksheet in the workbook. It performs the first task of inserting a line on all sheets but it does not finish the rest of the code on the rest of the worksheets. I've made the first line that is skipped blue.

Any suggestions?

Sub AM4_ReRecord()'
' AM4_ReRecord Macro
'


'
    Dim ws As Worksheet
    Dim Lastrow As Long
    
    Application.ScreenUpdating = False




    For Each ws In ActiveWorkbook.Worksheets


 


        With ws
            Lastrow = Range("BB2").End(xlDown).Row
            ws.Columns("BC:BC").Insert Shift:=xlToRight
            Range("BC1").Value = ">10%"


[COLOR=#0000ff][B]            With Range("BC2:BC" & Lastrow)[/B][/COLOR]
[COLOR=#0000ff][B]            .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"[/B][/COLOR]
[COLOR=#0000ff][B]            End With[/B][/COLOR]


            With Range("BS1").Value = ">10%"
            End With
            
            With Range("BS2:BS" & Lastrow)
            .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
            End With
 
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BC2"), SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, CustomOrder:="no,yes", DataOption:=xlSortNormal
                                 
            With .Sort
                .SetRange Parent.Range("AO:BC")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


            ws.Sort.SortFields.Add Key:=.Range("AQ2"), SortOn:=xlSortOnValues, _
                                 Order:=xlDescending, DataOption:=xlSortNormal


            With .Sort
                .SetRange Parent.Range("AO:BC")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
        
            ws.Sort.SortFields.Clear
            ws.Sort.SortFields.Add Key:=.Range("BS2" _
                                             ), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="no,yes", _
                                             DataOption:=xlSortNormal
            ws.Sort.SortFields.Add Key:=.Range("BH2" _
                                             ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


            With .Sort
                .SetRange Parent.Range("BE:BS")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

You haven't fully qualified your ranges, try

Sub AM4_ReRecord() '
' AM4_ReRecord Macro
'


'
   Dim ws As Worksheet
   Dim Lastrow As Long
   
   Application.ScreenUpdating = False
   
   For Each ws In ActiveWorkbook.Worksheets
   
      With ws
         Lastrow = .Range("BB2").End(xlDown).Row
         .Columns("BC:BC").Insert shift:=xlToRight
         
         .Range("BC1").Value = ">10%"
         With .Range("BC2:BC" & Lastrow)
            .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
         End With
         
         .Range("BS1").Value = ">10%"
         With .Range("BS2:BS" & Lastrow)
            .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
         End With
         
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=.Range("BC2"), SortOn:=xlSortOnValues, _
         Order:=xlAscending, CustomOrder:="no,yes", DataOption:=xlSortNormal
         
         With .Sort
            .SetRange Parent.Range("AO:BC")
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
         
         
         .Sort.SortFields.Add Key:=.Range("AQ2"), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
         
         
         With .Sort
            .SetRange Parent.Range("AO:BC")
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
         
         
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=.Range("BS2" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="no,yes", _
            DataOption:=xlSortNormal
         .Sort.SortFields.Add Key:=.Range("BH2" _
               ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
         
         
         With .Sort
            .SetRange Parent.Range("BE:BS")
            .header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
         
      
      End With
   Next ws
   Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Just a quick look so there may be other issues, but your ranges need qualifiers. For example, note the dots (.) in bold red font below:

With ws
            Lastrow = .Range("BB2").End(xlDown).Row
            .Columns("BC:BC").Insert Shift:=xlToRight
            .Range("BC1").Value = ">10%"


            With .Range("BC2:BC" & Lastrow)
                .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
            End With
'rest of code

 

[shebe228]

Just a quick look so there may be other issues, but your ranges need qualifiers. For example, note the dots (.) in bold red font below:

Thank you!! Is there any reason to not include the periods? I'm attempting to teach myself at a level far beyond where anyone would actually need to begin, so I'm missing a lot of basics.

 

[Fluff]

If you don't include the periods then you will be working on the Active sheet rather than on the ws worksheet.
A with statement is a form of shorthand so you could either do this

 With ws
         Lastrow = .Range("BB2").End(xlDown).Row
         .Columns("BC:BC").Insert shift:=xlToRight
         
         .Range("BC1").Value = ">10%"
         With .Range("BC2:BC" & Lastrow)
            .FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"
         End With

or do

   Lastrow = ws.Range("BB2").End(xlDown).Row
   ws.Columns("BC:BC").Insert shift:=xlToRight
   
   ws.Range("BC1").Value = ">10%"
   ws.Range("BC2:BC" & Lastrow).FormulaR1C1 = "=IF(RC[-9]>10%,""yes"",""no"")"

both of which are effectively the same