# Delete worksheets that are no longer required



## TedX (Dec 15, 2022)

Hi Everyone,

I'd like to create a macro, but I am not sure how to go about it. I have a workbook that I update every day which creates some new worksheets and corresponding new connections. Please see the image below. After I have taken a copy of the relevant data, I have to delete the worksheets and Connections each day manually. The worksheets always have 'Append1', whilst the other worksheets I don't need all follow the naming convention of 'Table 5' as an example. I never know how many tables there will be, each day is different, but they are all variations of the word Table a space and a number. It's the same with the connections. Can anyone create the VBA code for this macro for me please?


----------



## DanteAmor (Dec 15, 2022)

Try this:


```
Sub deleteSheets()
  Dim i As Long
  Application.DisplayAlerts = False
  For i = Sheets.Count To 1 Step -1
    If Sheets(i).Name Like "Table*" Then
      Sheets(i).Delete
    End If
  Next
End Sub
```


----------



## TedX (Dec 15, 2022)

DanteAmor said:


> Try this:
> 
> 
> ```
> ...



That's excellent, I wasn't sure if the wild card * needed an extra space or not and if I could string two commands together or not, so I used your code which deleted the Tables that Started with 'Table" and then I made a second version of your code and changed the word to read 'Append1', which also worked. I then made one macro which called them both and threw an icon on the ribbon and now it's a simple one-mouse click to remove all the tables I don't need every day, so thank you for that.

Regarding the Connections that are also created every day, I assume that these can't be deleted in the same manner, otherwise you would have included it. It would be handy if I could figure out a small macro that deletes these as well and I could add that to my other macros.


----------



## DanteAmor (Dec 15, 2022)

TedX said:


> Regarding the Connections that are also created every day


I assumed that they were going to be deleted with delete the sheet.
You could activate the macro recorder:
- Delete a query
- Delete a connection
- Delete a "Table"
- Delete an "append"
You stop the recorder, copy the generated code and paste it here.


----------



## TedX (Dec 15, 2022)

DanteAmor said:


> I assumed that they were going to be deleted with delete the sheet.
> You could activate the macro recorder:
> - Delete a query
> - Delete a connection
> ...



I tried that without luck, perhaps I shouldn't have selected all the connections and deleted them in one pass but rather deleted one at a time. I'll have to set it up again with new connections and try that. What I did get when I did it was weird.


```
Sub deleteConnections()
'
' deleteConnections Macro
'

'
    Application.CommandBars("Queries and Connections").Visible = False
End Sub
```


----------



## DanteAmor (Dec 15, 2022)

TedX said:


> I'll have to set it up again with new connections and try that.


You must create the sheets, the connections and the queries, then activate the macro recorder, delete the queries and the connections. Copy the macro code and paste it here.


----------



## TedX (Dec 15, 2022)

DanteAmor said:


> You must create the sheets, the connections and the queries, then activate the macro recorder, delete the queries and the connections. Copy the macro code and paste it here.



This is a lot of code and probably unmeaningful to you:


```
Sub TheWholeProcess()
'
' TheWholeProcess Macro
'

'
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 5", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data5 = Source{5}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data5, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 6", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data6 = Source{6}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data6, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Table 7", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://racingaustralia.horse/ozracing/Acceptances.aspx?key=2022Dec16,QLD,Rockhampton""))," & Chr(13) & "" & Chr(10) & "    Data7 = Source{7}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Data7, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""No."", Int64.Type}, {""Horse"", type text}," & _
        " {""Trainer"", type text}, {""Jockey"", type text}, {""Barr"", Int64.Type}, {""Wgt"", type number}, {""Pen"", type text}, {""RTG"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Queries.Add Name:="Append1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.Combine({#""Table 0"", #""Table 1"", #""Table 2"", #""Table 5"", #""Table 6"", #""Table 7""})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(Source,{""No."", ""Trainer"", ""Jockey"", ""Barr"", ""Wgt"", ""Pen""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_1"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 2"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 2]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_2"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 5"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 5]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_5"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 6"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 6]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_6"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 7"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 7]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_7"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Append1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Append1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Append1"
        .Refresh BackgroundQuery:=False
    End With
    Sheets(Array("Append1", "Table 7", "Table 6", "Table 5", "Table 2", "Table 1", _
        "Table 0")).Select
    Sheets("Table 0").Activate
    ActiveWindow.SelectedSheets.Delete
End Sub
```


----------



## DanteAmor (Dec 15, 2022)

But that is to create the queries and the connections, I asked you for the code to *delete* the queries and the connections.
And if before deleting the sheet, you simply delete the content of the entire sheet, will that delete the queries and the connections?
Try the following:


```
Sub deleteSheets()
  Dim i As Long
  Dim sh As Worksheet
  Application.DisplayAlerts = False
  For i = Sheets.Count To 1 Step -1
    Set sh = Sheets(i)
    If sh.Name Like "Table*" Or sh.Name Like "Append*" Then
      sh.Cells.Clear
      sh.Delete
    End If
  Next
End Sub
```


----------



## TedX (Dec 15, 2022)

DanteAmor said:


> But that is to create the queries and the connections, I asked you for the code to *delete* the queries and the connections.
> And if before deleting the sheet, you simply delete the content of the entire sheet, will that delete the queries and the connections?
> Try the following:
> 
> ...



Yes, that is much better, both "Table*" Or "Append*" in one single line of code, is excellent. Thank you, that has cleaned up the code from 3 macros down to just one. I appreciate your time and knowledge, and I am glad you didn't scream at me


----------



## DanteAmor (Dec 15, 2022)

TedX said:


> and I am glad you didn't scream at me


“*In* *space* *no* *one* *can* *hear* *you* *scream*.”


----------



## TedX (Dec 15, 2022)

Hi Everyone,

I'd like to create a macro, but I am not sure how to go about it. I have a workbook that I update every day which creates some new worksheets and corresponding new connections. Please see the image below. After I have taken a copy of the relevant data, I have to delete the worksheets and Connections each day manually. The worksheets always have 'Append1', whilst the other worksheets I don't need all follow the naming convention of 'Table 5' as an example. I never know how many tables there will be, each day is different, but they are all variations of the word Table a space and a number. It's the same with the connections. Can anyone create the VBA code for this macro for me please?


----------



## DanteAmor (Dec 15, 2022)

TedX said:


> Thank you, that has cleaned up the code from 3 macros down to just one.


Im glad to help you.

*Note*: You should mark as an answer the post that actually answered your question. Unless your own answer is the solution.


----------



## TedX (Dec 15, 2022)

DanteAmor said:


> “*In* *space* *no* *one* *can* *hear* *you* *scream*.”



LOL, finally I have met another crazy person, you have made my day, thank you again  🙏


----------

