Register
Log In
Unanswered
Home
June 27, 2016
Offline
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?
January 18, 2015
Offline
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
Most Users Ever Online: 57
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Max Neumann
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
