CODEWITHSUNDEEP
excelvba
TZPike05
TZPike05

Reputation: 1188

How can I copy a sheet and have my macros still work?

First off I want to apologize if my question has already been answered elsewhere as I did spend some time searching.

I'm using two macros in a sheet (very simple macros... they filter a table) and this sheet is a template that will be used every week. When I copy the sheet and make a new one, the macros no longer work. The error I receive is Runtime Error 9; Subscript out of range. I looked at the VBA code (which I've never really learned VBA) and see it is referencing a table title. Is there a way to fix this so it doesn't reference that specific table but rather the cells contained in that table? Example of the title name:

ActiveSheet.ListObjects("Table1619").Range.AutoFilter Field:=1

I want to access the data from a cell range of A103:A113. I tried this:

ActiveSheet.ListObjects.Range("$A$103:$A$113").AutoFilter Field:=1

This didn't work either but the error I received was different. "Run-time error '438': Object doesn't support this property or method"

What I know would work is if there was a way to have the same table name across all sheets but my limited research has seemed to point me in the direction of this not being possible.

Upvotes: 2

Views: 1549

Answers (2)

Peter Albert
Peter Albert

Reputation: 17475

The problem you're facing is that you access the Range through a Table/ListObject - but the ListObject changes the name during the copy, as each Table must have a unique name. The solution is simple - instead of accessing the ListObject with its name, simply use the index in the worksheet - that will not change. Therefore, replace 

ActiveSheet.ListObjects("Table1619").Range.AutoFilter Field:=1

with 

ActiveSheet.ListObjects(1).Range.AutoFilter Field:=1
(assuming it is the only/first table in the worksheet.)

Upvotes: 2

Peter L.
Peter L.

Reputation: 7304

It's impossible to reference ActiveSheet.ListObjects.Range("$A$103:$A$113") like this - either as above in your first sample, OR convert table to normal range and then use ActiveSheet.Range("$A$103:$A$113").

Upvotes: 2

Related Questions