If ActiveSheet.ListObjects = True ... Not Working

[ryguy7272]

I'm trying to use the macro below to disable the List feature which is
applied to some worksheets and it is really turning out to be a PITA.
Anyway, I recorded two small macros and got this code:
Range("A3").Select
ActiveSheet.ListObjects("Table1").Unlist

Range("M3").Select
ActiveSheet.ListObjects("List2").Unlist

I tried to modify this in my code below. The macro below will loop through
all sheets, no problems there. However, it will NOT disable that stubborn
List feature in each sheet...whcih in some sheets seems to be a table
("Table1") and in other sheets seems to be a specific list ("List2")


Sub Macro1()
For Each sh In ThisWorkbook.Worksheets
If ActiveSheet.AutoFilterMode = True Then
Rows("1:1").Select
Selection.AutoFilter
If ActiveSheet.AutoFilterMode = False Then
If ActiveSheet.ListObjects = True Then
ActiveSheet.ListObjects.Unlist
If ActiveSheet.ListObjects = False Then
End If
End If
End If
End If
Next
End Sub


I'd appreciate any and all help.

Regards,
Ryan---

 

[Jim Rech]

Sub DelLists()
Dim WS As Worksheet
Dim LS As ListObject
For Each WS In Worksheets
For Each LS In WS.ListObjects
LS.Unlist
Next
Next
End Sub

--
Jim
| I'm trying to use the macro below to disable the List feature which is
| applied to some worksheets and it is really turning out to be a PITA.
| Anyway, I recorded two small macros and got this code:
| Range("A3").Select
| ActiveSheet.ListObjects("Table1").Unlist
|
| Range("M3").Select
| ActiveSheet.ListObjects("List2").Unlist
|
| I tried to modify this in my code below. The macro below will loop
through
| all sheets, no problems there. However, it will NOT disable that stubborn
| List feature in each sheet...whcih in some sheets seems to be a table
| ("Table1") and in other sheets seems to be a specific list ("List2")
|
|
| Sub Macro1()
| For Each sh In ThisWorkbook.Worksheets
| If ActiveSheet.AutoFilterMode = True Then
| Rows("1:1").Select
| Selection.AutoFilter
| If ActiveSheet.AutoFilterMode = False Then
| If ActiveSheet.ListObjects = True Then
| ActiveSheet.ListObjects.Unlist
| If ActiveSheet.ListObjects = False Then
| End If
| End If
| End If
| End If
| Next
| End Sub
|
|
| I'd appreciate any and all help.
|
| Regards,
| Ryan---
|
| --
| RyGuy

 

[ryguy7272]

That's exactly what I was looking for! Thanks so much Jim!!!

In all honesty, where do you find this stuff? I have some books, but I
don't think any of those references explain this syntax. The macro recorder
is great...up to a point. Google is powerful...sometimes...


Anyway, thank you very much!

Since you were so helpful with this issue, I have one more question, which
pertains to another post
http://www.microsoft.com/office/com...b2f9b9bb42d&cat=&lang=en&cr=US&sloc=en-us&p=1

I have about 85 variables, some are just slightly different names in
different columns, which of course is interpreted differently by Excel when
trying to build a Pivot Table.

It was suggested that I:
a)order columns as suggested below
b)run though each required column and if column does not exist in sheet then
insert an empty one


Regards,
Ryan---