0

I have a really tricky bug in my macro project.

At one point in the project I take a large dynamic range and convert it into a filterable table. I need to run several passes on this table, which each require a removal of duplicates in a different column. For this reason, I'm using multiple copies of the sheet.

Now here lies the problem;

The table in the master sheet (let's say "table1") is being copied over perfectly! Except that it is being named "table12" some of the time, and depending how many times the user runs the script, it will create tables named "table13" . . . 14 . . . 15 etc.

Obviously I am using filters on the table which require the table to be named exactly under listobjects, and as such I get a failure if the script is run more than once in the workbook.

I am really stumped for workarounds, and have found that hiding the duplicate rows in the master sheet, rather than creating copies and deleting them, to cause more issues than it solves.

Does anyone know of a more elegant solution to the problem, or any recommendations on how to do the hiding of duplicate rows more efficiently? Thanks in advance if you do!

Lewis Heslop
  • 574
  • 2
  • 8
  • 25

1 Answers1

0

You can use the .Listobject property of the Range object:
e.g.(If A1 is in the Table)

Set copiedListobject = Sheets("CopiedSheet").Range("A1").Listobject
Siphor
  • 2,522
  • 2
  • 13
  • 10
  • Great answer! I could have used this perfectly, instead I opted to convert to range and back to a newly named table, that also worked. – Lewis Heslop Aug 12 '14 at 09:39