I am trying to rewrite this code to work on a table imported from Power Query:
*The bold text is where its failing, I guess to not using a range, but I am unable to understand what .Cells.Autofilter is even doing
Appreciate any help
*The bold text is where its failing, I guess to not using a range, but I am unable to understand what .Cells.Autofilter is even doing
VBA Code:
Dim wsSrc As Worksheet
With wsSrc
If .FilterMode Then .ShowAllData
If .AutoFilterMode = False Then .Cells.AutoFilter 'This line is where its having the error
Select Case .CodeName
Case "Tool1", "Tool2", "Tool3", "Tool4", "Tool5"
lngSortCount = 1
Case Else
lngSortCount = 2
End Select
If .CodeName <> "Summary" Then
If .FilterMode Then .ShowAllData
For i = 1 To lngSortCount
.AutoFilter.Sort.SortFields.Clear
Select Case .CodeName
Case "Tool6"
Set rngWorking = .Columns(2 * i).EntireColumn
Case "Tool2", "Tool3", "Tool4", "Tool5"
Set rngWorking = .Columns(2).EntireColumn
Case "Tool7"
Set rngWorking = .Columns(1).EntireColumn
Case Else
Set rngWorking = .Columns(i + 1).EntireColumn
End Select
.AutoFilter.Sort.SortFields.Add Key:=rngWorking, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next i
End If
.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Copy
End With
Appreciate any help
Last edited: