VBA How to Create an Array of ListObjects

Question

I am trying to write a Macro that goes through all of the Tables in a workbook and then adds the name of each table in the form of "TableName[#All]" to an array.

See incomplete code below

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

I have only come to the conclusion that it must operate this way because if a user adds a worksheet then later deletes it, the PivotTableWizard errors out.

I am not sure if doing it this way each time will erase the formatting the PivotTable has.

Kevin Kramer · Accepted Answer · 2015-08-21 21:33:02Z

1

The answer was:

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

answered Aug 21, 2015 at 21:33

Kevin Kramer

411 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mrlemmer11 · Accepted Answer · 2015-08-20 22:22:08Z

0

Dim tbl As ListObject
For Each tbl In ActiveSheet.ListObjects
    'Do stuff to tbl here
Next tbl

Also, I would recommend this site if you are going to be working with tables a lot. http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

answered Aug 20, 2015 at 22:22

mrlemmer11

3574 silver badges16 bronze badges

1 Comment

Kevin Kramer Over a year ago

Thanks, but the "do stuff here" part is really what I need. This doesnt solve my main question of how to add each of those tables to an array.

Collectives™ on Stack Overflow

VBA How to Create an Array of ListObjects

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related