CODEWITHSUNDEEP
excelvba
jules325
jules325

Reputation: 179

Creating multiple tables with a macro

I have a workbook in which each worksheet has identically formatted data but for different entities. I'd like to make each of these in to a table, but I'm unfamiliar with how to write anything like this. There's too many sheets to make doing this by hand an efficient option. Below is my attempt at coding this but it's pretty broken. Again, I've never actually tried to do anything like this.

My intention is that I would have this workbook open, I'd run the macro, and it would loop through each sheet and create a table for the present data. Then I would play around with the pivot table I'd use these table for. Can anyone offer some help, please? 

Sub Create_Tables()

    Dim wrksht As Worksheet

    For Each sht In ActiveWorkbook.Worksheets

        With ActiveSheet
           .ListObjects.Add(xlSrnRange, Range("AE1", Range ("AE1").End(xlToRight).End(xlDown)) , , xlYes)
           .Name:= .Name & "_Table"

           .ListObjects(.Name & "_Table").TableStyle = "TableStyleLight1"

    Next sht

End Sub

Upvotes: 0

Views: 3199

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

This tested code will work. Hopefully you can discern the slight modifications I have made.

Option Explicit

Sub Create_Tables()

    Dim wrksht As Worksheet

    For Each wrksht In ThisWorkbook.Worksheets

        With wrksht

            Dim myTable As ListObject

            Set myTable = .ListObjects.Add(xlSrcRange, wrksht.Range("AE1", wrksht.Range("AE1").End(xlToRight).End(xlDown)), , xlYes)

            With myTable
                .Name = .Name & "_Table"
                .TableStyle = "TableStyleLight1"
            End With

        End With

    Next wrksht

End Sub

Upvotes: 1

Related Questions