Hi All,
I know im getting closer, but not close enough obviously!
I have written the code below, but have been getting a Compile Error: Object Required. Are you able to help me out in fixing it?
Basically, I want the code to change the column that is auto filtered depending on a specific customer (Need column 7 filtered) in a cell on another worksheet. That specific customer is one of 4 different customer that will be entered in that cell (all other customers need column 6 filtered).
I know im getting closer, but not close enough obviously!
I have written the code below, but have been getting a Compile Error: Object Required. Are you able to help me out in fixing it?
Basically, I want the code to change the column that is auto filtered depending on a specific customer (Need column 7 filtered) in a cell on another worksheet. That specific customer is one of 4 different customer that will be entered in that cell (all other customers need column 6 filtered).
Code:
Dim celltxt As String
Set wsLiab = ActiveWorkbook.Sheets("Liability report")
Set wsDataInput = ActiveWorkbook.Sheets("Data Input")
Set celltxt = wsDataInput.Range("B4").Text
With Sheets("Liab A")
With .Range("A1:N" & Sheets("Liab A").Range("A" & Rows.Count).End(xlUp).Row)
If InStr(1, celltxt, "BS1") Then
.AutoFilter Field:=7, Criteria1:="=S*", _
Operator:=xlAnd
.AutoFilter Field:=10, Criteria1:="<>0", _
Operator:=xlAnd
Else
.AutoFilter Field:=6, Criteria1:="=S*", _
Operator:=xlAnd
.AutoFilter Field:=10, Criteria1:="<>0", _
Operator:=xlAnd
End If
End With
.Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(1).SpecialCells(xlCellTypeVisible).Copy
Sheets("Liability report").Range("A21").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
wsLiab.Select
Lastrow = Range("A21").End(xlDown).Row
Range("C21:W21").AutoFill Destination:=Range(Range("C21"), Range("W" & Lastrow))
wsLiab.Calculate
End Sub