2

I have created some automation to sort/filter columns in an Excel table, but one of the columns is constantly changing on a weekly basis. See 'Week 24' in code below. How can I reference a cell here so it's being updated accordingly?

Sub WKTOJ_HiLo()
'
' WKTOJ_HiLo Macro
'

'
    ActiveWorkbook.Worksheets("TOJ by Employee").ListObjects("Table1").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("TOJ by Employee").ListObjects("Table1").Sort. _
        SortFields.Add Key:=Range("Table1[[#All],[Week 24]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("TOJ by Employee").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=Range("E7").Value, _
        Criteria1:="<>", Operator:=xlFilterValues
End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
Matt
  • 23
  • 2

1 Answers1

1

If the code works, then pass the column as a variable, based on the current week with WorksheetFunction.WeekNum(date). Add this to the code:

Dim weeklyRange as String
Dim week as Long

currentWeek = WorksheetFunction.WeekNum(date)
weeklyRange = "Table1[[#All],[Week " & currentWeek & "]]"

ActiveWorkbook.Worksheets("TOJ by Employee").ListObjects("Table1").Sort. _
SortFields.Add Key:=Range(weeklyRange), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

It is a good idea to avoid ActiveWorkbook and ActiveSheet in Excel:

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    Thank you so much for this. My report is delayed by a week, so I just added "-1" to the WeekNum and all is good. Again, thank you. – Matt Jun 18 '19 at 19:48