Filtering a table by clicking on column header

[mark hansen]

I have a data set that was just a plain data set (not a table). I set up a macro in the Worksheet_SelectionChange event that when I clicked on the column header it would sort on that column. Now the data set is a Table because I need to replicate formulas to get additional information from the data. I recorded the sort in the table and compaired it with the code I set up, and the only difference is the Key:= parameter.

In my (non table) code the sort line is

  Range(Cells(20, ActiveCell.Column), Cells(100000, ActiveCell.Column)), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

You can see I use the Activecell.column to tell the code what column to use.

the recorded Table code is

ActiveWorkbook.Worksheets("Data").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[[#All],[CM]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal

How can I change the Key:= parameter to have a variable column?

Thanks
Mark

 

[Akuini]

Try:

Key:=ActiveCell

 

[mark hansen]

Thanks Akuini!!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.