maracleinc
maracleinc

Reputation: 27

Converting all data to tables on all worksheets in workbook

I have a workbook with 84 worksheets all with different amounts of rows of data. I need to convert all of the worksheets data to tables.

I found this macro online which I thought would work if I had all of the sheets selected but it doesn't.

Sub A_SelectAllMakeTable2()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
End Sub

Is there a way to modify this so it will affect the entire workbook?

Upvotes: 2

Views: 3910

Answers (1)

Rory
Rory

Reputation: 34085

You could use something like this:

Sub A_SelectAllMakeTable2()
    Dim ws as worksheet
    for each ws in activeworkbook.worksheets
    ws.ListObjects.Add(xlSrcRange, ws.range("A1").Currentregion, , xlYes).TableStyle = "TableStyleMedium15"
    next ws
End Sub

Upvotes: 4

Related Questions