问题

I have little experience with loops/arrays in VBA. I am looking to copy all tabs that start with "FY" in the name to a new wb. Where I am having difficulty is adding the tabs to the array to copy based on criteria as I do not know nor have been able to find the syntax for this.
I added in the MsgBox line as a placeholder.

Sub Generate_Report()
Dim current As Worksheet
Dim currentname As String

'----Add tab name to array if starts with 'FY'----
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
    MsgBox ("Add this tab to the array")
Next

End Sub`

Sub Generate_Report()
Dim current As Worksheet
Dim currentname As String

'----Add tab name to array if starts with 'FY'----
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
    MsgBox ("Add this tab to the array")
Next

End Sub`

答案1

得分: 1

If you are going to use an array you could do something like this:

如果要使用数组，你可以像这样做：

Dim demo1(10) As Worksheet
Dim apointer As Integer
apointer = 0

Dim current As Worksheet, currentname As String
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
        Set demo1(apointer) = current
        apointer = apointer + 1
    End If
Next

The main problem here is the maximum number of sheets you can store is 11 (the array runs from cell 0 to cell 10 by default).

这里的主要问题是你最多只能存储11个工作表（数组默认从单元格0到单元格10）。

You might be tempted to use a "dynamic" array E.G.:

你可能会诱惑使用一个“动态”数组，例如：

Dim demo1() As WorkSheet

Dim apointer As Integer
apointer = 0

Dim current As Worksheet, currentname As String
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
        ReDim Preserve demo1(apointer)
        demo1(apointer) = current
        apointer = apointer + 1
    End If
Next

But it would probably be far better to use a Collection (or a Dictionary), or even a ListBox. See Collection Object.

但更好的做法可能是使用集合（或字典），甚至是列表框。请参阅Collection Object。

Dim demo1(10) As Worksheet
Dim apointer As Integer
apointer = 0

Dim current As Worksheet, currentname As String
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
        Set demo1(apointer) = current
        apointer = apointer + 1
    End If
Next

Dim demo1() As WorkSheet

Dim apointer As Integer
apointer = 0

Dim current As Worksheet, currentname As String
For Each current In Worksheets
    currentname = current.Name
    If Left(currentname, 2) = "FY" Then
        ReDim Preserve demo1(apointer)
        demo1(apointer) = current
        apointer = apointer + 1
    End If
Next