Overwrite sheet if it exists

Question

I recorded a macro that makes some pivot tables in a new sheet and it works fine, so i tried to make it able to overwrite if the sheet already existed but i can't seem to make it work. The problem is that when i use the macro once it does add the new sheet but it also adds another one on top of the one already made, and when i try to use the macro again to see if it overwrites the other one, it doesn't it just adds another unnamed sheet. The code looks like this:

Sub Makro7()

Sheets.Add After:=ActiveSheet
On Error Resume Next
Sheets.Add().Name = "Statistics"
On Error GoTo 0

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Base!R1C1:R18288C12", Version:=6).CreatePivotTable TableDestination:= _
    "Statistics!R1C1", TableName:="Pivottabel22", DefaultVersion:=6
Sheets("Statistics").Select
Cells(1, 1).Select
ActiveSheet.PivotTables("Pivottabel22").AddDataField ActiveSheet.PivotTables( _
    "Pivottabel22").PivotFields("FACULTY_ID"), "Antal af FACULTY_ID", xlCount
With ActiveSheet.PivotTables("Pivottabel22").PivotFields("FACULTY_ID")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("Pivottabel22").PivotFields("PROGRAM_TYPE_NAME")
    .Orientation = xlRowField
    .Position = 1
End With
Range("A1").Select
ActiveSheet.PivotTables("Pivottabel22").DataPivotField.PivotItems( _
    "Antal af FACULTY_ID").Caption = "Antal"
Range("B1").Select
ActiveSheet.PivotTables("Pivottabel22").CompactLayoutColumnHeader = "Fakultet"
Range("A7").Select
ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel22").PivotCache. _
    CreatePivotTable TableDestination:="Statistics!R7C1", TableName:= _
    "Pivottabel23", DefaultVersion:=6
Sheets("Statistics").Select
Cells(7, 1).Select
ActiveSheet.PivotTables("Pivottabel23").AddDataField ActiveSheet.PivotTables( _
    "Pivottabel23").PivotFields("FACULTY_ID"), "Antal af FACULTY_ID", xlCount
With ActiveSheet.PivotTables("Pivottabel23").PivotFields("PROGRAM_TYPE_NAME")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("Pivottabel23").PivotFields("FACULTY_ID")
    .Orientation = xlColumnField
    .Position = 1
End With
With ActiveSheet.PivotTables("Pivottabel23").PivotFields("Antal af FACULTY_ID")
    .Calculation = xlPercentOfTotal
    .NumberFormat = "0.00%"
End With
Range("A7").Select
ActiveSheet.PivotTables("Pivottabel23").DataPivotField.PivotItems( _
    "Antal af FACULTY_ID").Caption = "Procentvis"
Range("B7").Select
ActiveSheet.PivotTables("Pivottabel23").CompactLayoutColumnHeader = "Fakultet"
Range("A13").Select
ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel23").PivotCache. _
    CreatePivotTable TableDestination:="Statistics!R13C1", TableName:= _
    "Pivottabel24", DefaultVersion:=6
Sheets("Statistics").Select
Cells(13, 1).Select
ActiveSheet.PivotTables("Pivottabel24").AddDataField ActiveSheet.PivotTables( _
    "Pivottabel24").PivotFields("ENROLL_LOCATION_NAME"), _
    "Antal af ENROLL_LOCATION_NAME", xlCount
With ActiveSheet.PivotTables("Pivottabel24").PivotFields("ENROLL_LOCATION_NAME" _
    )
    .Orientation = xlRowField
    .Position = 1
End With
Range("B13").Select
ActiveSheet.PivotTables("Pivottabel24").DataPivotField.PivotItems( _
    "Antal af ENROLL_LOCATION_NAME").Caption = "Antal"
Range("A13").Select
ActiveSheet.PivotTables("Pivottabel24").CompactLayoutRowHeader = "Campus"
Range("B13").Select
ActiveSheet.PivotTables("Pivottabel24").DataPivotField.PivotItems("Antal"). _
    Caption = "Antal af studerende"
Range("A22").Select
ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel24").PivotCache. _
    CreatePivotTable TableDestination:="Statistics!R22C1", TableName:= _
    "Pivottabel25", DefaultVersion:=6
Sheets("Statistics").Select
Cells(22, 1).Select
ActiveSheet.PivotTables("Pivottabel25").AddDataField ActiveSheet.PivotTables( _
    "Pivottabel25").PivotFields("ENROLL_LOCATION_NAME"), _
    "Antal af ENROLL_LOCATION_NAME", xlCount
With ActiveSheet.PivotTables("Pivottabel25").PivotFields("ENROLL_LOCATION_NAME" _
    )
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("Pivottabel25").PivotFields( _
    "Antal af ENROLL_LOCATION_NAME")
    .Calculation = xlPercentOfTotal
    .NumberFormat = "0.00%"
End With
Range("A22").Select
ActiveSheet.PivotTables("Pivottabel25").CompactLayoutRowHeader = "Campus"
Range("B22").Select
ActiveSheet.PivotTables("Pivottabel25").DataPivotField.PivotItems( _
    "Antal af ENROLL_LOCATION_NAME").Caption = "Procentvis af studerende"
Range("I1").Select
ActiveWorkbook.Worksheets("Statistics").PivotTables("Pivottabel25").PivotCache. _
    CreatePivotTable TableDestination:="Statistics!R1C9", TableName:= _
    "Pivottabel26", DefaultVersion:=6
Sheets("Statistics").Select
Cells(1, 9).Select
ActiveSheet.PivotTables("Pivottabel26").AddDataField ActiveSheet.PivotTables( _
    "Pivottabel26").PivotFields("STUDYBOARD_ID"), "Antal af STUDYBOARD_ID", xlCount
With ActiveSheet.PivotTables("Pivottabel26").PivotFields("STUDYBOARD_ID")
    .Orientation = xlRowField
    .Position = 1
End With
Range("I1").Select
ActiveSheet.PivotTables("Pivottabel26").CompactLayoutRowHeader = "Studienævn"
Range("J1").Select
ActiveSheet.PivotTables("Pivottabel26").DataPivotField.PivotItems( _
    "Antal af STUDYBOARD_ID").Caption = "Antal af studerende"
Range("L15").Select

End Sub

Answer 1

The problem is this piece of code:

Sheets.Add After:=ActiveSheet
On Error Resume Next
Sheets.Add().Name = "Statistics"
On Error GoTo 0

This basically tells Excel to add a new sheet after the active sheet and when you run the macro the second time, the sheetname "Statistics" is already taken. (And if it wasn't for On error resume next, an error message would appear the second time). Add this at the top of your macro instead:

Dim newSheet As Worksheet

Application.DisplayAlerts = False

Set newSheet = Sheets.Add(After:=ActiveSheet)
With newSheet
    On Error Resume Next
    ThisWorkbook.Sheets("Statistics").Delete
    On Error GoTo 0
    .name = "Statistics"
End With

Application.DisplayAlerts = True