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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
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

R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps
E
  • Solved
A little VBA/Macro code adjustment
Replies
3
Views
293
rollis13
rollis13
S
  • Question Question
Trouble with macro to delete rows
2
Replies
14
Views
246
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
270
MARK858
MARK858
G
  • Question Question
Sort (Z-A) Macro not working
Replies
1
Views
93
NateSC
NateSC

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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