Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If ActiveSheet.ListObjects = True ... Not Working
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If ActiveSheet.ListObjects = True ... Not Working
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 "ryguy7272" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If ActiveSheet.ListObjects = True ... Not Working
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/comm...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--- -- RyGuy "Jim Rech" wrote: 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 "ryguy7272" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with Macros - ActiveSheet.Paste | Excel Programming | |||
ActiveSheet.HPageBreaks not working | Excel Programming | |||
ACTIVESHEET.PASTE not working. | Excel Programming | |||
ActiveSheet.Next.Select not working! | Excel Programming | |||
ActiveSheet.Protect UserinterfaceOnly:=true | Excel Programming |