VBA create and sort report

[HakaWaka]

Hi all,

Im not much of a VBA user but had recorded this macro. The purpose of this macro is to produce a report which uses pivot table data which gets put into a new report in a new worksheet, then once in a new worksheet the data gets sorted by column B first and then column A second, lastly after all that the worksheet gets renamed to "CodeReport".

What ive found was that once i run the macro the following error appears "Run-time error '9': Subscript out of range" and then when i click the Debug button the Red bold text below is highlighted in the VBA window. I had noticed that everytime i run the macro a new worksheet is created to output the pivot table report which is fine but the worksheet name keeps changing eg Sheet1....Sheet2.....Sheet3.....Sheet4 etc everytime i run the macro hence why i manually added the blue code below to keep the worksheet name constant as "Sheet2".

Any idea how to get around this issue?

THANK YOU


Sub Report()
'
' Report Macro
' Creates a report based on pivot table data, renames the new worksheet and sorts the report data
'
'
Range("L15").Select
Selection.ShowDetail = True
ActiveSheet.Name = "Sheet2"
Sheets("Sheet2").Select
ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[Charge Code]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[Flag]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "CodeReport"
End Sub