Copy Sheets Failed (Excel 2000)

Topic

New Reply

Howdy…I figure I’m either bumping up into some limitations in Excel, or (more likely) some limitations in my coding ability! Perhaps someone in the Lounge could help…

I have a spreadsheet with a tab called MasterDoc. The associated VBA Code has a loop that performs some calculations/updates to this MasterDoc worksheet, and then copies this MasterDoc worksheet into a new workbook, renames the tab in the new workbook, and then repeats this loop for the next store. It should loop through 12 stores.

Windows(“Sale 2002 – v.1.10.xls”).Activate
Sheets(“MasterDoc”).Select
‘Copy the Master worksheet to the interim Store Qty CommunicationMaster file
‘for each store, then save that file with the final unique filename including Region, District and Store
Sheets(“MasterDoc”).Copy Before:=Workbooks(“Store Qty CommunicationMaster.xls”). _
Sheets(1)

This works perfectly for the first 8 times through this iteration, but then fails on the 9th iteration…the error message is “Run-time Error ‘1004’: Copy method of worksheet class failed.” So when it crashes there are nine worksheets – Sheet1, plus my 8 copied/renamed worksheets, and it seems to fail when attempting to add the next worksheet. The Visual Basic Editor is pointing to the last line in the code sample shown above.

Is there some sort of limit in Excel to the number of worksheets I can add this way? More likely, I’ve just chosen some bad coding practices! Can anyone provide any suggestions?

Thanks,
Trev.

Reply | Quote

Replies

It may have to do something with the code name of the sheets.

Excel worksheets have two names: the name you see on the worksheet tab and an internal code name. This code name is assigned automatically. If a lot of copying has been going on, this code name may become too long.

You can see and modify the code name in the Visual Basic Editor:

Switch to the VBE (Tools/Macro/Visual Basic Editor or Alt+F11).
In the Project Explorer, expand your workbook, then Microsoft Excel Objects.
For each worksheet, you’ll see something like Sheet11111 (MasterDoc).
In this example, MasterDoc is the tab name, and Sheet11111 is the code name.
If you see very long code names, modify them in the Properties window.
The (Name) property is the code name, while the Name property is the tab name.

Unfortunately, the code name is a read-only property in VBA (in Excel 97, probably hasn’t changed in Excel 2000), so you can’t set it in your macro.

HTH,
Hans

Reply | Quote

Hans,

The codename may be readonly officially, but I find the following code works provided of course that the new name does not already exist in the project.

Dim ws As Worksheet
Set ws = ActiveSheet
ThisWorkbook.VBProject.VBComponents(ws.CodeName).Properties(“_CodeName”).Value = “NewName”

Andrew

Reply | Quote

Thanks Hans and Andrew…in the Project Explorer, the sheet names are all a max of 3 characters after the word ‘sheet’.

I’ll see if I can recreate this problem in a non-confidential workbook, and if so, I’ll post it back here…in the meantime, any other suggestions are very welcomed!!!

Cheers,
Trev.

Reply | Quote