XL2007 .CodeName bug when copying sheet

R

Reinhard Thomann

My application uses .CodeName property to identify XL sheets.
When copying a sheet within a workbook the new sheets gets the same CodeName
plus Index.
This fails in XL2007 SP1.

Steps to reproduce bug:

1. Create new workbook
2. Modify .CodeName property of sheet 1 (e.g. test) in VBE
3. Save workbook
4. Close workbook and open it again (don't start VBE (F11))
5. Copy sheet 1
6. Check .CodeName property of copied sheet
7. BUG: CodeName is not e.g. test1

Does anybody knows a workaround to this problem?

TIA
Reinhard
 
R

Reinhard Thomann

Hi OssiMac,

you have to copy the sheet imediately after opening workbook (Point 4.) .
Don't start VBE (F11) before.
If VBE is stared before copying works correctly!?
I tried it many times with different file formats (2007 and 2003). Always
the same behaviour.

Reinhard
 
O

OssieMac

Hi Reinhard,

Your are right. Also you can copy as many times as you like and it is still
Sheet1, Sheet4, Sheet5 etc. (I had 3 sheets to start with). After opening and
closing VBA editor then it will show Test1,Test2 etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XL2007 .CodeName bug when copying sheet 4
Hierarchy of Codename and error catching if no worksheet. 1
Passing sheet (using codename) to separate sub as optional paramet 7
2 Copy Sheet ?'s 10
Remove VB code when copying sheet to new workbook 2
Copy a Sheet to an AddIn 2
Add New Sheet when Multiple Sheets are Selected 2
Excel VBA won't copy Worksheets after sheet Deletion 5

Top