每次单击宏按钮都会给工作表提供新的名称。

Question

我正在为与工作相关的数据管理创建一个自动跟踪器。我有一个工作表，其中包含所有按钮，下面的按钮与排列宏相关联。

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

每次我点击按钮，它就会创建两个新的页面，这是我想要的，但是另一方面，它给出了随机的名称，比如Sheet 21和Sheet 22。在删除它并重新运行之后，它将给第23页和第24页.我想给这些床单起一个明确的名字。

Answer 1

如评论所述，请注意链接posted、How to avoid using Select in Excel VBA中的建议，其中多个答案建议不要使用.Select、.Activate、ActiveWorkbook、ActiveSheet以及由于隐式引用、上下文依赖、运行时错误、性能和可重现性而产生的所有变体。

相反，对于用例，使用Set操作符显式地分配对象。然后调整或调用对象所需的底层属性和方法：

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