用于表格格式的 VBA。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44779251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA for Table Formatting.
提问by Mikz
I have sheet1, sheet2 , sheet3, sheet4.
我有 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.
在 4 个工作表中，工作表 1 和工作表 2 的列表中有数据。并且 sheet3 和 sheet 4 具有相同的数据透视表。
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.
我想要一个 VBA，以这样的方式，在我的工作簿中，如果它找到带有列表的表格，那么它应该将其格式化为表格。该表格应仅适用于它具有价值的单元格。
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(":48576"), , xlYes).Name = _
"Table2"
Cells.Select
ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight9"
End Sub
回答by Shai Rado
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
回答by Dy.Lee
If your question is that change range to Listobject, look at follow code.
如果您的问题是将范围更改为 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
