I am trying to write a code to sort data in multiple tables, each table has the same headers. The issue I am having is that every week I create a new tab by copying the previous weeks tab which renames the table, all the table header names are the same in each table in the workbook. The code I have references the original table causing a run time error. There is only one table on each sheet. How can I write the code to reference the headers within the table on individual sheets?
This is what I currently have, the original table was named "JobList"
Sub Sort_Spreadsheet()
' Sort to input data from Weekly Spreadsheet
ActiveSheet.ListObjects(1).Sort. _
SortFields.Clear
ActiveSheet.ListObjects(1).Sort. _
SortFields.Add Key:=Range("JobList[Status]"), SortOn:=xlSortOnValues _
, Order:=xlDescending, DataOption:=xlSortNormal
ActiveSheet.ListObjects(1).Sort. _
SortFields.Add Key:=Range("JobList[Proj '#]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects(1).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
This is what I currently have, the original table was named "JobList"
Sub Sort_Spreadsheet()
' Sort to input data from Weekly Spreadsheet
ActiveSheet.ListObjects(1).Sort. _
SortFields.Clear
ActiveSheet.ListObjects(1).Sort. _
SortFields.Add Key:=Range("JobList[Status]"), SortOn:=xlSortOnValues _
, Order:=xlDescending, DataOption:=xlSortNormal
ActiveSheet.ListObjects(1).Sort. _
SortFields.Add Key:=Range("JobList[Proj '#]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects(1).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub