VBA - Cria uma matriz de todos os nomes de tabelas na pasta de trabalho

Question

Meu objetivo final aqui é ter um botão que, quando pressionado, crie uma nova planilha com uma tabela. Essa nova tabela deve ser adicionada à lista do Assistente de Tabelas Dinâmicas.

O erro que está sendo chamado é

mensagem de erro 1004 PivotTableWizard método de planilha falhou

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").Select
Sheets("CE_Template").Copy After:=Sheets("Project Estimator")
ActiveSheet.Name = NewShop

If ActiveSheet.Name = "CE_Template" Then
    Exit Sub
Else
    Range("C4").Value = NewShop
    ActiveSheet.ListObjects(1).Name = NewShop
End If

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

Sheets("Project Estimator").Select
Range("A17").Select
ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=MyArray

Edit: minha pergunta anterior era como adicionar itens ao array, que já foi resolvido. Obrigado. Por favor, veja uma nova pergunta.

    

Answer 1

Isso resolverá seu problema imediato, você precisa adicionar esta linha antes do seu loop:

ReDim MyArray(0 To 0)

Quando você Dim MyArray() está criando uma matriz sem índices, não é possível atribuir nenhum valor a ela sem usar ReDim primeiro.

Você também precisa alterar o ReDim em seu loop para ReDim Preserve . Se você usar apenas ReDim , ele destruirá o conteúdo de sua matriz toda vez.