I have a workbook which contains one report. Each day I copy and paste this report in a new sheet. I've been updating this workbook and here's my problem. I use a table, and have some auto sorting taking place. Whenever i create a new sheet, My table name is static at "Table1". Can i use VBA to to auto create or change or follow the table number so my code will function?
Code:
Option Explicit
Sub SortLog()
With Application
.ScreenUpdating = False
.EnableEvents = False
With ActiveSheet.ListObjects("Table1").Sort
With .SortFields
.Clear
.Add Key:=Range("A7"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
With .SortFields
.Clear
.Add Key:=Range("B7"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
End With
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.EnableEvents = True
.ScreenUpdating = False
End With
End Sub