Forum Discussion

katrina bethea's avatar
katrina bethea
Brass Contributor
Aug 09, 2018

macro messing up and i need help understanding VBA

this is not the entire vba as its a long recorded macro but when using step into this is the section that messes up. it hits the same error further down the sheet when it recorded the same filter but on the ("12 month") report. i've bolded and italicized the part the debugger says is messing up.

 

"Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveWorkbook.Worksheets("3 MONTH").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("3 MONTH").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("3 MONTH").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply"

 

the other odd part about this is that it worked on the exact same reports at my house but when i transfer my macros i record at home to my office i run into this problem. 

 

basically what i need the sheet to do is sort from lowest to highest on column 1 and have the rest of the information throughout the rest of the rows to follow with its corresponding information in column 1. (sorting products the we sell by SKU number, needs to be done on both sheets that are within the workbook) i can attach my sheets if need or will help.

Macros and VBA
  • katrina bethea's avatar
    katrina bethea
    Brass Contributor
    Aug 09, 2018

    i've deleted the sections within the macro that tell it to sort and everything else works fine. could someone help me with coding that will select "3 month" sheet and filter column "A" lowest to highest (with the rest of the sheet's data following with its corresponding sku in column "A"). 

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Aug 10, 2018
    ActiveWorkbook.Worksheets("3 MONTH").AutoFilter.Sort.SortFields.Add2 Key:= _
    Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

    declare
    Dim Lastrow as long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    I suspect that the Range is not declared right.. I think it should be Range("A1:A"&Lastrow)
    then after
    With ActiveWorkbook.Worksheets("3 MONTH").AutoFilter.Sort
    you should declare the entire range say,
    .SetRange Range("A1:S" & LastRow) ** where S is your last column

    HTH

Resources