用于表格格式的VBA .-Java 学习之路

我有sheet1，sheet2，sheet3，sheet4 .

在4张表中，表1和表2列出了数据 . 并且sheet3和sheet 4具有相同的Pivot表 .

我希望有一个VBA，以这种方式，在我的工作簿中，如果它找到带有列表的表格，那么它应该将其格式化为表格 . 该表应仅适用于具有值的单元格 .

我使用了记录宏来获取代码，但我很震惊我应该如何为我的所有表格实现它 . 代码，从一张表的记录宏：

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

Normally, when i copy from data source, it resembles like below image

I want a VBA that changes the above figure like this without Manual Operation.

2 回答

我认为你的意思类似于下面的代码：

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

回复于 2024-05-17T17:10:58+08:00

如果您的问题是Listobject的更改范围，请查看以下代码 .

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

回复于 2024-05-17T17:10:58+08:00

用于表格格式的VBA .

2 回答

相关问题