Lope Autofill Macro through all sheets

[shebe228]

This code is SOOOOO close to being right, but I cannot for the life of me figure out what I am missing. The part where I autofill the formula to the last row only works on the first sheet and not the rest. I'm sure its something dumb that I'm missing because I am new to the last row function. Any suggestions?

Sub AM4_2()
'
' AM4_2 Macro
'
' Adds two new columns with if statements for less than 10%.  Sorts by Yes-Less than 10% then by Bill Count and Allowed
'
    Dim ws As Worksheet
    Application.ScreenUpdating = False


    For Each ws In ActiveWorkbook.Worksheets


        With ws
            .Columns("BC:BC").Insert Shift:=xlToRight
            .Range("BC1").Value = ">10%"


            With .Range("BC2")
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
            End With
            
            With .Range("BC2")
            Lastrow& = Range("BA:BB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("BC2").AutoFill Destination:=Range("BC2:BC" & Lastrow)
                .Value = .Value
            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

            With .Range("BS2")
        .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
        Lastrow& = Range("BP:BQ").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("BS2").AutoFill Destination:=Range("BS2:BS" & Lastrow)
                .Value = .Value
            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
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

This line shouldn't have a With before it

[COLOR=#0000ff]With [/COLOR].Range("BS1").Value = ">10%"

 

[shebe228]

Yes! I did find that and correct it. I do have one more question though...

I have 47 client tabs with a different number of rows on each sheet. The first sheet works perfectly, however when it loops to the next sheet instead of looking for the last row on the current sheet it is performing on, it appears to be pulling the count of rows throughout the whole workbook which in some cases is giving more than 50,000 extra rows between my "no's" and "yes's". Is there a way to adjust this to where it goes to the last row of each specific sheet?

Sub AM4_IN_Filter_Discounts()
'
' AM4_IN_Filter_Discounts
'
' Adds two new columns with if statements for less than 10%.  Sorts by Yes-Less than 10% then by Bill Count and Allowed
'
    Dim ws As Worksheet
    Dim LastRow As Long
    
    Application.ScreenUpdating = False




    For Each ws In ActiveWorkbook.Worksheets




[B]        With ws[/B]
[B]            .Columns("BC:BC").Insert Shift:=xlToRight[/B]
[B]            .Range("BC1").Value = ">10%"[/B]
[B]            LastRow = .Range("BA:BB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/B]

[B]            With .Range("BC2:BC" & LastRow)[/B]
[B]                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"[/B]
[B]                .Value = .Value[/B]
[B]            End With[/B]
            
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("BC2"), SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, CustomOrder:="no,yes", DataOption:=xlSortNormal
            .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


           
            With .Range("BS1").Value = ">10%"
            End With
            
                        
[B]            With .Range("BS2:BS" & LastRow)[/B]
[B]                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"[/B]
[B]                .Value = .Value[/B]
[B]            End With[/B]




            .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
    Application.ScreenUpdating = True
End Sub
[\code]

 

[Fluff]

This

With .Range("BS1").Value = ">10%"
            End With

Should be

.Range("BS1").Value = ">10%

As far as the lastrow goes, it's looking in cols BA:BB for the last row of data on each sheet. If this is giving a false reading, do you have anything in those columns below the data, maybe cells with a blank "space", or a formula returning ""?

 

[shebe228]

Not in columns BA:BB. There may be in other columns.

 

[Fluff]

In that case I'm not sure. There's nothing wrong that I can spot.
If you step through your code line by line using F8. When LastRow is set for each sheet, check that it is giving the correct value.
Would you be able to shar your workbook, by uploading to Dropbox or OneDrive, mark for sharing & post the link here?

 

[shebe228]

I will try to create a dummy file this morning.

 

[JLGWhiz]

Disregard