VBA Format data as table and filter

[Rachel1994]

Hello!

I'm writing a macro to make a data output look nicer for users. I have formatted the data as a table, and now I want to sort the data on two things: first Supplier Name, then Product Description (These are the headers).

I set up the table as follows:

Dim tbl As ListObject
Dim rng As Range

Set rng = Range(Range("A1"), Range("a1").SpecialCells(xlLastCell))
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleLight1"

I've tried various bits of code to sort this but keep getting errors and not sure where I'm going wrong. 

e.g.:

With tbl.Sort
.SortFields.Add Key:=Range("C1"), Order:=xlAscending        'This sorts on supplier name
.SortFields.Add Key:=Range("D1"), Order:=xlAscending        'This sorts on product description
.SetRange (rng)
.Header = xlYes
.Apply
End With

Any help very much appreciated!

[#2 · Accepted solution]

@Rachel1994 

Does this work for you?

tbl.Range.Sort key1:=Range("C1"), order1:=xlAscending, key2:=Range("D1"), order2:=xlAscending, Header:=xlYes

[#3]

Yes this works perfectly, thanks so much for your help!

[#4]

You're welcome @Rachel1994! Glad it worked as desired.