Macro to apply filters to multiple tables

Hi all,

I've had a search around the forums and have read alot of the threads relating to this, but I belive my problem is a bit of a niche, so here it goes!

Every month I compile a report on excel. I am in the process of setting up macros to automate this building so every month all i need to do is open the spreadsheet file, refresh the data connection & run the macros to have the full report built.

The data connection is an SQL query and creates a table on Sheet1 with all the data. The macro I am building duplicates & renames this sheet 7 times, and the next part of the macro must apply different filters to each table on each sheet.

My problem is that everytime I run the macro to duplicate the sheets it will give the table in each sheet a different name, therefore making the macro which is applying the filters spit out a "Run-time error 9".

My question is: How do I edit the macro to refer not to a specific table name, but to the only table contained in the sheet(as the sheets contain only 1 table)?

Here is the macro:

Please Login or Register  to view this content.

Basically, I need the "ActiveSheet.ListObjects("Table_Query_from_Remedy_717181920212223").Range" to refer to the only table in the worksheet.

Please tell me if i am not being clear and i will attempt to rectify.

Thanks in advance to anyone that helps

Nima

Nima

Try using the index rather than the name. Completely untested but try

Please Login or Register  to view this content.

rylo

Rylo, thanks for the reply.

No luck with that thought, I receive a "Run-time error 5, Invalid procedure call or argument"

This was my code:

Please Login or Register  to view this content.

The debugger highlights the entire bit of code with an arrow next to the xlFilterValues line.

Any idea where to take it from here?

Lol so i just realised that you meant put in "activesheet.listobjects(1).range" not add the range of the table.


This works like a charm Rylo , tyvm!