Cannot Copy Some Worksheet Tabs

  • Thread starter Thread starter Alasdair
  • Start date Start date
A

Alasdair

I have a fairly large spreadsheet with tabs for each
month of the year. As this has developed over the past
couple of years, the number of tabs has grown and I now
cannot copy some of the tabs to the end as a template for
next month's sheet.

Older tabs seem to copy better than newer tabs and I have
no hidden worksheets (per article on KnowledgeBase).

Any ideas ?
 
Hi,

This implies that the workbook contains sheets with
illegal code names.
Probably the offending sheet is the copy of the copy of
the copy of .... a sheet and the workbook has originaly
been created in XL97.

These illegal names stem from a bug in XL 97 and surface
when you copy a sheet. XL97 adds a 1 to the code name
(that is NOT the name you see on the sheet's tab) of the
copy, so it's name becomes Sheet11. Copying that sheet
gives you Sheet111, etcetera.

Remedy:

1. Open the file in Excel 97
2. Open the VBE (Alt-F11)
3. Find the file's project in the project explorer
4. Expand the project
5. Make sure the properties window is visible (view menu)
6. Click a sheet
7. Check out the (Name) property, if it looks like the one
stated, change it to something shorter (and unique!)
8. Repeat step 6 for all sheets
9. Close the VBE
10.Save the workbook

In future, use the SAME sheet over and over to create the
copies.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Jan,

Many thanks for this - you're spot on !

The file was originally created in XL97, but has
subsequently been modified in XL2000, however, the Code
Names that you indicated are present.

Many thanks,

Alasdair
 
Back
Top