Hello,
In VBA I want to copy a worksheet with a table to the same workbook, and then
rename the new worksheet and table. Renaming the worksheets easy enough, but
I'm not so sure about the table. When I record a macro to copy the worksheet
the table name tblDrugsTracker is copied to tblDrugsTracker7. How can my VBA code
anticipate that the copied table will be called tblDrugsTracker7?
For that matter, why does macro recording not appear to generate any VBA code
to do with tables?
The table will appear in the ListObjects collection.
You should be able to loop through all the ListObjects on the new sheet to determine the name(s).
By comparing with the name(s) on the original sheet you should be able to identify the new name(s) and then rename as required.
For example, something like the following:
Dim objX As Object
For Each objX In ActiveSheet.ListObjects
MsgBox objX.Name
' add code here to compare & rename
Next objX
Thanks for this, Derek.
