Autofilter with variable/cell as Criteria

Question

I want to use autofilter function but I want to take the Criteria1 from the cell from another Worksheet. I tried to use the the exact range and also a variable. I become the error run time error 9 subcript out of range by the line. Set GL = Worksheets("Sheet1").Range("l7").Value. Is it even possible? Should I use another type of variable? At the beggining I am in worksheet("DE-CC") and the criteria I take from Worksheet("Sheet1"), cell "L7". Any sugestions?

Dim GL As Range
Set GL = Worksheets("Sheet1").Range("l7").Value
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AB1").Select
    Selection.AutoFilter
    ActiveSheet.Range("a2:ab2").AutoFilter Field:=19, Criteria1:=GL

Mikku Mikku · Accepted Answer · 2019-07-11T10:20:49

This should Work:

(Untested)

Dim GL As String

GL = Worksheets("Sheet1").Range("l7").Value

    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Activesheet.UsedRange.AutoFilter Field:=19, Criteria1:=GL

Changed the Declaration and assigning of Value

You should Try this Also,

Dim GL As String

GL = Worksheets("Sheet1").Range("l7").Value

    Activesheet.UsedRange.AutoFilter Field:=19, Criteria1:=GL

Autofilter with variable/cell as Criteria

1 Answers