Lope Autofill Macro through all sheets

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
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?

Rich (BB code):
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try this syntax

Code:
With .Range("BC2")
    LastRow& = .Range("BA:BB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .AutoFill .Range("BC2:BC" & LastRow)
        With .Range("BC2:BC" & LastRow)
            .Value = .Value
        End With
End With
 

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
It works for the first sheet but when it gets to the second sheet it says there is an error
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
It works for the first sheet but when it gets to the second sheet it says there is an error

What is the error message and which line of code is highlighted when you click the debug button?
 

shebe228

New Member
Joined
Sep 28, 2017
Messages
46

ADVERTISEMENT

What is the error message and which line of code is highlighted when you click the debug button?


Run-time error '1004':

AutoFill method of Range class failed. The underlined line


Code:
        With .Range("BC2")
            Lastrow& = Range("BA:BB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[U][B]            .AutoFill .Range("BC2:BC" & Lastrow)[/B][/U]
                With .Range("BC2:BC" & Lastrow)
                    .Value = .Value
                End With
        End With
[\Code]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,990
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
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
    Dim LastRow As Long
    
    Application.ScreenUpdating = False


    For Each ws In ActiveWorkbook.Worksheets


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

            With .Range("BC2:BC" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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
           
           LastRow = Range("BP:BQ").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With .Range("BS2:BS" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Run-time error '1004':

AutoFill method of Range class failed. The underlined line


Code:
        With .Range("BC2")
            Lastrow& = Range("BA:BB").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[U][B]            .AutoFill .Range("BC2:BC" & Lastrow)[/B][/U]
                With .Range("BC2:BC" & Lastrow)
                    .Value = .Value
                End With
        End With
[\Code][/QUOTE]

Little syntax problem, this should work.
[CODE]LastRow& = .Range("BA:BB").Find("*", , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Range("BC2").AutoFill .Range("BC2:BC" & LastRow)
        With .Range("BC2:BC" & LastRow)
            .Value = .Value
        End With
 

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
Try
Code:
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
    Dim LastRow As Long
    
    Application.ScreenUpdating = False


    For Each ws In ActiveWorkbook.Worksheets


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

            With .Range("BC2:BC" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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
           
           LastRow = Range("BP:BQ").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With .Range("BS2:BS" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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



Thank you!!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,990
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
I attempted to clean up where I enter the same formula in column BC to BS. Now I am getting a "Next without For" Error. Any suggestions?

Code:
Sub AM4_2new()
'
' AM4_2new 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
    Dim LastRow As Long
    
    Application.ScreenUpdating = False




    For Each ws In ActiveWorkbook.Worksheets




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


            With .Range("BC2:BC" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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("BS1").Value = ">10%"


            With .Range("BS2:BS" & LastRow)
                .FormulaR1C1 = "=IF(RC[-1]>10%,""yes"",""no"")"
                .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
 

Forum statistics

Threads
1,144,392
Messages
5,724,081
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top