Lope Autofill Macro through all sheets

S

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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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
 
Upvote 0
shebe228 said:
It works for the first sheet but when it gets to the second sheet it says there is an error
Click to expand...

What is the error message and which line of code is highlighted when you click the debug button?
 
Upvote 0
JLGWhiz said:
What is the error message and which line of code is highlighted when you click the debug button?
Click to expand...


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]
 
Upvote 0
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
 
Upvote 0
shebe228 said:
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
Click to expand...
 
Upvote 0
Fluff said:
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
Click to expand...



Thank you!!!!!!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Column sort
Replies
9
Views
106
Fluff
Fluff
Dazzawm
  • Question
Change Recorded Range To Used Range.
2
Replies
12
Views
367
Dazzawm
Dazzawm
O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
380
mumps
mumps
J
  • Question
Rename a Sheet
Replies
2
Views
108
Peter_SSs
Peter_SSs
Dazzawm
  • Solved
Slight Amendment To Code
Replies
6
Views
292
Dazzawm
Dazzawm

Forum statistics

Threads
1,214,925
Messages
6,122,303
Members
449,078
Latest member
nonnakkong

Share this page

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
Back
Top