vba 添加多个工作表，命名它们，并将粘贴动态范围复制到新工作表中

Add multiple sheets, name them, and copy paste dynamic range into new sheets

提问by Kazimierz Jawor

I'm new to excel and I'm trying to add multiple sheets, name each one. The macro is only adding one sheet at a time, example I will click "run" and it will create the "Price Adjustment" table but no others. When I click "run" again it will create the following table only, and so on.

我是 excel 新手，我正在尝试添加多张工作表，并为每个工作表命名。宏一次只添加一张纸，例如我将单击“运行”，它将创建“价格调整”表，但不会创建其他表。当我再次单击“运行”时，它只会创建下表，依此类推。

Sub NewSheets()

With Sheets.Add()
    .Name = "CustomerTable"
    .Name = "EmployeeTable"
    .Name = "OrdersTable"
    .Name = "ProductTable"
    .Name = "PriceAdjustment"
End With

End Sub

Thanks

谢谢

回答by Kazimierz Jawor

The quickest improvement of the code is to move Add() methodinside With...End Withstatement like this:

代码的最快改进是像这样移动Add() method内部With...End With语句：

Sub NewSheets()

With Sheets
    .Add().Name = "CustomerTable"
    .Add().Name = "EmployeeTable"
    .Add().Name = "OrdersTable"
    .Add().Name = "ProductTable"
    .Add().Name = "PriceAdjustment"
End With

End Sub

回答by ApplePie

This is because you are calling the Add()method once. Try this:

这是因为您正在调用该Add()方法一次。尝试这个：

Sub AddNewWorksheet(name as String)
    With Worksheets.Add()
        .Name = name
    End With
End Sub

Then you can add worksheets just like this:

然后你可以像这样添加工作表：

AddNewWorksheet("CustomerTable")
AddNewWorksheet("EmployeeTable")
'...

回答by chuff

Another way to go about this is to put the new sheet names into an array and then loop through the array to create all five of your tables at once.

解决此问题的另一种方法是将新工作表名称放入一个数组中，然后遍历该数组以一次创建所有五个表。

Couple of things to note about the code:

代码中需要注意的几点：

• The array shArrayfor the sheet names is declared as a Variant so that we can populate the array with the Arrayfunction without having to loop through the array to assign each element.

• In setting up the Forloop, I use the LBoundand UBoundfunctions to calculate the index numbers for the first and last elements of the array. That way, it's not necessary to keep track of the number of array elements if the number changes.

  Option Explicit                        'Turn on compiler option requiring
                                         'that all variables be declared
  Sub NewSheets()
  
    Dim shArray() As Variant             'Declare the sheet Name array and a
    Dim i As Long                        'counter variable
  
    shArray = Array("CustomerTable", _
                    "EmployeeTable", _
                    "OrdersTable", _
                    "ProductTable", _
                    "PriceAdjustment")   'Populate the array
  
    For i = LBound(shArray) To UBound(shArray)  'Loop through the elements
        Sheets.Add().Name = shArray(i)           
    Next i
  
  End Sub
  

• shArray工作表名称的数组被声明为 Variant，这样我们就可以用Array函数填充数组，而不必遍历数组来分配每个元素。

• 在设置For循环时，我使用LBound和UBound函数来计算数组的第一个和最后一个元素的索引号。这样，如果数量发生变化，就没有必要跟踪数组元素的数量。

  Option Explicit                        'Turn on compiler option requiring
                                         'that all variables be declared
  Sub NewSheets()
  
    Dim shArray() As Variant             'Declare the sheet Name array and a
    Dim i As Long                        'counter variable
  
    shArray = Array("CustomerTable", _
                    "EmployeeTable", _
                    "OrdersTable", _
                    "ProductTable", _
                    "PriceAdjustment")   'Populate the array
  
    For i = LBound(shArray) To UBound(shArray)  'Loop through the elements
        Sheets.Add().Name = shArray(i)           
    Next i
  
  End Sub