How to reference current week cell in column when sorting a Excel table with VBA?

user_11666450 0
Viewed 61

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
vba excel sorting
1 Answers

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:

user_54486260
Related