我想在表列中创建所有工作表的索引列表及其名称。
到目前为止,我已经编写了以下代码,但是在引号上给出了错误。
Dim ws As Worksheet, tbl As ListObject, i As Integer
Set ws = Sheets("Profile Management")
Set tbl = ws.ListObjects("sheets")
With tbl.ListRows
Do While .Count >= 1
.Item(1).Delete
Loop
End With
For i = 1 To Sheets.Count
"tbl.ListColumns(1).DataBodyRange = Sheets(i).Name"
Next I
我要去哪里错了?
使用结构化(又称为ListObject)表会给VBA带来一些其他问题。您不能以这种方式写入.DataBodyRange属性,并且.DataBodyRane是ListObject的成员,而不是ListObject的ListColumns属性。
Option Explicit
Sub wqwe()
Dim tbl As ListObject, i As Long, w As Long
With Worksheets("Profile Management")
With .ListObjects("sheets")
'make sure there is at least 1 row in the databodyrange
If .DataBodyRange Is Nothing Then _
.ListRows.Add
'clear the first column
.DataBodyRange.Columns(1).ClearContents
'insert the worksheet names
For w = 1 To Worksheets.Count
'except "Profile Management"
If Worksheets(w).Name <> .Parent.Name Then
i = i + 1
'expand the table for new worksheets
.DataBodyRange.Cells(i, 1) = Worksheets(w).Name
'optionally insert a hyperlink to each worksheet's A1
.Parent.Hyperlinks.Add Anchor:=.DataBodyRange.Cells(i, 1), _
Address:=vbNullString, SubAddress:=Worksheets(w).Name & "!A1", _
TextToDisplay:=Worksheets(w).Name, ScreenTip:="click to go there"
End If
Next w
'reshape the table if there are blank rows
Do While i < .ListRows.Count
.ListRows(i + 1).Delete
Loop
End With
End With
End Sub
如上面的注释中所述,我已经添加了直接从表中的列表直接链接到每个工作表的选项。如果选择此路由,则不必先将名称放入表格单元格中。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句