Clicking macro button is giving new name to sheet everytime

Question

I am creating a automated tracker for work related data management. I have one sheet which contains all the button out of which the below is associated with the arrange macro.

Sub Arrange() 

    Sheets("Consolidated Sheet").Select
    ActiveWorkbook.Queries.Add Name:="Invoked Function", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = KMIP_Cleanup(Query2)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    ActiveWorkbook.Queries.Add Name:="Arranged Links", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Arranged_Data(#""Invoked Function"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"

    ActiveWorkbook.Worksheets.Add

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Invoked Function"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Invoked Function]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Invoked_Function"
        .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=""Arranged Links"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Arranged Links]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Arranged_Links"
        .refresh BackgroundQuery:=False        
    End With

    Sheets("Macros").Select
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

Every time I click the button it creates two new sheets which is what I want but on the other hand it gives random name like Sheet 21 and Sheet 22. And after deleting it and re-running it will give Sheet 23 and Sheet 24. I want to give defined name to these sheets.

Every worksheet has a `.Name` property. You may also have a look on [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/17172829) — Shrotter, Oct 08 '22 at 09:38

score 1 · Answer 1 · answered Oct 08 '22 at 16:44

As commented, heed the recommendations in linked posted, How to avoid using Select in Excel VBA, where multiple answers advise not to use .Select, .Activate, ActiveWorkbook, ActiveSheet and all is variants due to implicit reference, context dependence, runtime errors, performance, and reproducibility.

Instead, for your use case explicitly assign objects with Set operator. Then adjust or call the needed underlying properties and methods of object:

Dim wks As Worksheet
...

Set wks = ThisWorkbook.Worksheets.Add
wks.Name = "MySheet1"

With wks.ListObjects.Add
   ...
End With


Set wks = ThisWorkbook.Worksheets.Add
wks.Name = "MySheet2"

With wks.ListObjects.Add
   ...
End With

Clicking macro button is giving new name to sheet everytime

1 Answers1