0

I am working on a for loop that takes an entry on the first page of a worksheet, and copy and pastes it to the remaining sheets in the workbook, with the array size being Worksheets.Count. Specifically, I am having trouble finding a way to call on each table in each specific worksheet. I was trying to select the table based on the counter in the for loop such that: 

For i=1 to Worksheets.count Range.("Table(Counter").Select Next i

but it doesn't work. Is there a better way to make sure that my code selects the proper table in each successive worksheet? I am still pretty new to VBA, so any and all help is appreciated!

Improve this question

1 Answer 1

Reset to default
1

If you are using the counter, I am assuming your tables are named Table1, Table2, and so on for each worksheet. The problem here is that you need to make sure that the Worksheet Index is in the same sequence as your table.

You can try this alternative which is not dependent on the sequence of the worksheet and name of the Tables.

Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.ListObjects(1).Range.Select ' to check if you got the correct object, add below line Debug.Print sh.ListObjects(1).Range.Address(, , , True) Next

Above is considering you only have one(1) Table in each Sheet.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Great, Thanks! The only issue is that after the first loop it doesn't go on to the next sheet in the workbook book. How would I fix that?
@Jamie What do you mean it doesn't go to the next sheet. That will iterate every worksheet in the workbook.
After it iterates through the first table, it selects the next table on the next sheet, but doesnt add the row. It gives an error "select method of range class failed". Again, thanks for the help so far and i hope this comment helps.
@chs I am now lost :) First, how many tables do you have in one sheet. Second, but doesnt add the row what do you mean by this statement? AFAIK, we're only trying to access the tables in each worksheet so other than that will be out of scope.
I ended up figuring it out, it ended up being as simple as adding a Worsheet.Activate piece. Thanks for your help

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.