VBA for sheet design and table style options, for application to whole workbook

[drawinginfinity]

Hi,
Hopefully you guys might be able to advise me, I've spent most of today looking over different VBA code but I've not found anything quite like what I'm looking to do.

I currently have a workbook whose individual sheets pull data from different excel workbooks on Sharepoint. When this data is pulled across, the design of the data is quite unusual, with an odd table style and table style options: a header row, banded rows and filter button.
The problem is that I am now leaving this position, and am unsure that those I am leaving this workbook with will be willing or able to remove these items from the incoming worksheets, which frequently come in numerous times a day. As such I am looking to create a macro that will cycle through the full workbook, reverting offending sheets to no table style, and removing the header row, banded rows, and filter button.
Apologies that I do not have code to edit, as I'm afraid I am quite new to VBA, and as I am sure you will appreciate, I am frankly unsure where even to begin with something as daunting as this.

Thank you for any advice you can provide.

 

[Fluff]

Hi & welcome to MrExcel.
Are you looking to convert the tables to normal ranges?

 

[drawinginfinity]

Hi & welcome to MrExcel.
Are you looking to convert the tables to normal ranges?

Hi Fluff, thank you for your response. Would this result in all of those changes detailed above occurring?
I only ask as this is required for other macros I have to run, specifically the unticking of the filter button, which does not appear to be the same as turning off filters.

if it would change all of these then converting to normal ranges would be great.
Many thanks

 

[Fluff]

Try this on a copy of your workbook & see if it does what you need

Sub drawinginfinity()
   Dim Ws As Worksheet
   Dim Tbl As ListObject
   
   For Each Ws In Worksheets
      For Each Tbl In Ws.ListObjects
         Tbl.TableStyle = ""
         Tbl.Unlist
      Next Tbl
   Next Ws
End Sub

 

[drawinginfinity]

Try this on a copy of your workbook & see if it does what you need

Sub drawinginfinity()
   Dim Ws As Worksheet
   Dim Tbl As ListObject
   
   For Each Ws In Worksheets
      For Each Tbl In Ws.ListObjects
         Tbl.TableStyle = ""
         Tbl.Unlist
      Next Tbl
   Next Ws
End Sub

Hi Fluff,
Thank you for this, though I think I misunderstood your initial query. The connections to the original source sheets would be required to be maintained. Your code did everything I needed it to do, is it possible for these changes to occur without breaking the connections?
Many thanks

 

[Fluff]

How about

Sub drawinginfinity()
   Dim Ws As Worksheet
   Dim Tbl As ListObject
   
   For Each Ws In Worksheets
      For Each Tbl In Ws.ListObjects
         Tbl.TableStyle = ""
         Tbl.ShowAutoFilterDropDown = False
         Tbl.ShowHeaders = False
      Next Tbl
   Next Ws
End Sub

 

[drawinginfinity]

How about

Sub drawinginfinity()
   Dim Ws As Worksheet
   Dim Tbl As ListObject
   
   For Each Ws In Worksheets
      For Each Tbl In Ws.ListObjects
         Tbl.TableStyle = ""
         Tbl.ShowAutoFilterDropDown = False
         Tbl.ShowHeaders = False
      Next Tbl
   Next Ws
End Sub

Hi Fluff, Thanks for coming back to me.
This code is kicking out an error "Run-time error '1004':
Application-defined or object-defined error.
Debugging highlights "Tbl.ShowAutoFilterDropDown = False"
do you have any thoughts as to why this might be or how to resolve it?
Many thanks for all your help

 

[Fluff]

Try changing that lien to

         If Tbl.ShowAutoFilter Then Tbl.ShowAutoFilterDropDown = False

 

[drawinginfinity]

This did the job perfectly, thank you!

 

[Fluff]

You're welcome & thank for the feedback