Addressing a ListObject Table dynamically

[ThumbsUp]

Hello all,

My first post on your excellent forum. As a new VBA user, I've been able to search and find all the answers I need...except one.

I have an Excel 2007 spreadsheet of data that is manipulated by VBA code to produce a Pivot Table. Then, it shows the Detail behind one of the Pivot Table subtotals by creating a ListObject Table. I then address that Table as a sheet and am able to print it.

My problem is that I cannot, for the life of me, figure out how to reference the Table so that it can be formatted to one of the Design styles offered in Excel 2007. Excel is naming the Table something like "Table8", increasing by one each time a Table is produced.

I need to be able to address the Table name dynamically so that no matter how many time the code is run, the reference to the Table is correct.

Here is a simple procedure to illustrate my problem:


Sub FormatDetailTbl()

ActiveSheet.ListObjects("Table8").TableStyle = "TableStyleLight9"

End Sub


Thanks for any help you can offer.

Greg

[Bob Phillips]

You need to set a listobject variable to that listobject when you create it, then reference that listobject variable.

[ThumbsUp]

And how would I set the variable without knowing what the name will be? It could be "Table8", "Table9", "Table3". Probably a stupid question, but at least I did confess to being a Newbie!

[Bob Phillips]

Show us the code where you create the listobject, and we should be able to help.

[ThumbsUp]

' Very long procedure, so I pick up where the PivotTable is already crerated and begin with it's naming

ActiveSheet.Name = "PT"
ActiveSheet.PivotTables("PivotTable5").PivotFields("Sold").ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields("Sold").CurrentPage = "N"

' Theis is where one of the subtotals in the Pivot Table is selected to show the detail behind it

Range("B5").Select
Selection.ShowDetail = True
Cells.Select
Cells.EntireColumn.AutoFit
ActiveSheet.Name = "Out of Date"
ActiveWorkbook.Worksheets("Out of Date").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Out of Date").Sort.SortFields.Add Key:=Range( _
"I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Out of Date").Sort
.SetRange Range("A2:L20950")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Here is the point where I'd like to format the Table.
' Here I continue with the printing of the Table, unfortunately using those "medium dark" blue tones that are the default.

Range("A1:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Set Print_Area = Selection

' The code continues with the actual printing of the Table, the sheet is renamed so that it will have a common name for the next step in the procedure, deletion.

[Bob Phillips]

I don't see anything creating a ListObject table there.

[ThumbsUp]

It's created by the line


Selection.ShowDetail = True

[Aflatoon]

Sub FormatDetailTbl()

ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight9"

End Sub

should work since it is a new sheet and only has one ListObject.

[ThumbsUp]

Thank you. I appreciate your help.
G