VBA 如何创建 ListObjects 数组

Question

我正在尝试编写一个宏，它遍历工作簿中的所有表，然后将每个表的名称以“TableName[#All]”的形式添加到数组中。

请参阅下面不完整的代码

Sub NewShopPage()
'
' Macro that creates a duplicate sheet from a hidden template
' searches for every table in the workbook and then adds them to the
' PivotTableWizard.
'

Dim NewShop As Variant
NewShop = InputBox("What Shop are you creating a new estimate sheet for? (example: Shop 18)")

NewShop = Replace(NewShop, " ", "_")

Sheets("CE_Template").Copy After:=Sheets("Project Estimator")
ActiveSheet.Name = NewShop


ActiveSheet.ListObjects(1).Name = NewShop

Sheets("Project Estimator").Select
ActiveSheet.ListObjects(1).Select

Dim MyArray As Variant

    'HELP NEEDED HERE:
    'For each [Table] in [workbook]
    '    Add TableName + "[#All]" to MyArray
    'Next

ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=MyArray


End Sub

我只是得出结论，它必须以这种方式操作，因为如果用户添加工作表然后删除它，数据透视表向导就会出错。

我不确定每次这样做是否会删除数据透视表的格式。

Answer 1

答案是:

Dim MyArray() As String
Dim ArraySize As Integer
Dim ws As Worksheet
Dim Tbl As ListObject

ArraySize = 0

ReDim MyArray(0 To 0)

For Each ws In ActiveWorkbook.Worksheets
    For Each Tbl In ws.ListObjects
        ReDim Preserve MyArray(ArraySize) As String
        MyArray(UBound(MyArray)) = Tbl.Name & "[#All]"
        ArraySize = ArraySize + 1
    Next Tbl
Next ws