时间戳

首页 > 解决方案 > 如何将工作表设置为变量以获取更多方法（带循环）

vba - 如何将工作表设置为变量以获取更多方法（带循环）

问题描述

我使用此代码从一个范围创建了一个工作表（这对我有用）

Sub AddSheets1()

Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
Dim xlWs1 As Worksheet

Set xlWs1 = Worksheets("template")
Set Rng1 = xlWs1.Range("A1:AR2")

For Each cell In wsWithSheetNames.Range("G3:G5") 'user to change this value when list is updated
    With wbToAddSheetsTo
        .Sheets.Add after:=.Sheets(.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = cell.Value

        Set xlWs2 = Worksheets(CStr(cell.Value))
        Set Rng2 = xlWs2.Range("A1")
        Rng1.Copy Rng2

        If Err.Number = 1004 Then
          Debug.Print cell.Value & " already used as a sheet name"
        End If
        On Error GoTo 0
    End With
Next cell

End Sub

所以现在，我想为新创建的工作表分配变量。（这样我就可以使用 ws1、ws2 等进一步操作。类似的东西

Dim i As Integer, n As Integer
n = wsWithSheetNames.Range("G3:G5").Count
ReDim ws1(1 To n)
For i = 1 To n
    Set ws1(i) = ThisWorkbook.Worksheets(i)
Next

如何将工作表名称分配给我从范围创建的名称？

标签： vbaexcel

解决方案

假设可能有比 G3:G5 中指定的更多的工作表，也许这会起作用。

Dim r As Range, c As Range, x As Long
Set r = wsWithSheetNames.Range("G3:G5").SpecialCells(xlCellTypeConstants)
ReDim ws1(1 To r.Count)
For Each c In r
  x = x + 1
  Set ws1(x) = ThisWorkbook.Worksheets(c.Value)
Next c

推荐阅读