June 27, 2016
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?
January 18, 2015
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
' add code here to compare & rename
Most Users Ever Online: 57
Currently Online: Matthias
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
R vd Bosch
Rao Sharafat Ali
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas