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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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

E
  • Solved
A little VBA/Macro code adjustment
Replies
3
Views
279
rollis13
rollis13
S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
265
MARK858
MARK858
N
  • Question Question
ActiveX Option Button to sort Data in another sheet and update conbobox
2 3
Replies
22
Views
262
NUC_N_FUTS2
N
P
  • Solved
Macro to scroll to the last coloured cell in a spreadsheet
Replies
4
Views
394
Joe4
Joe4
H
  • Solved
Column sort
Replies
9
Views
510
Fluff
Fluff

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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