VBA for Table Formatting.

Question

I have sheet1, sheet2 , sheet3, sheet4.

Of the 4 Sheets, sheet 1 and sheet2 has data in list. and sheet3 and sheet 4 has Pivot tables for the same.

I would like to have a VBA, in such a way that, in my workbook, if it find Sheets with list, then it shoudl Format it to table. The table should be only for the cells it has value.

I used record macro, to get the code, but i am struck how i should implement it for all my Sheets. the code, from record macro for one sheet:

sub macro()
  Cells.Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$1:$1048576"), , xlYes).Name = _
        "Table2"
    Cells.Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight9"
End Sub

Answer 1

I think you meant something like the code below:

Option Explicit

Sub macro()

Dim ws As Worksheet
Dim ListObj As ListObject

For Each ws In ThisWorkbook.Worksheets
    With ws
        For Each ListObj In .ListObjects
            ListObj.TableStyle = "TableStyleLight9"
        Next ListObj
    End With
Next ws

End Sub

Answer 2

If your question is that change range to Listobject, look at follow code.

Sub macro()
    Dim Ws As Worksheet
    Dim LstObj As ListObject
    Dim rngDB As Range, n As Integer

    For Each Ws In Worksheets
        With Ws
            Set rngDB = .Range("a1").CurrentRegion
            For Each LstObj In Ws.ListObjects
                LstObj.Unlist
            Next
            If WorksheetFunction.CountA(rngDB) > 0 Then
                n = n + 1
                Set LstObj = .ListObjects.Add(xlSrcRange, rngDB, , xlYes)
                With LstObj

                    .Name = "Table" & n
                    .TableStyle = "TableStyleLight9"
                End With
            End If
        End With
    Next Ws

End Sub