Unable to copy worksheet

[Bill Wittmer]

I am using Excel 97 to create a daily manpower and work assingment
sheet. We have created a running workbook, adding a new manpower
worksheet for each progressive day. We have created a Master Manpower
sheet as the first worksheet page. The method is to then copy the
Master worksheet and the make adjustments and changes in the manpower
for a particular shift and create a new manpower roster for the new
day. The copied and adjusted manpower sheet is then renamed and saved
withing the workbook. The name used for each new worksheet is based on
shift/day/month/year, which results in a 7 digit number. We have been
doing the successfully for several months until a problem arose. When
we go to copy a master sheet to create a new manpower worksheet, we get
the following error message, "An application error has occurred and an
application error log has been generated." "Excel.exe " "Exception
access violation (0x0000005) address 0x3023b43e." The Escel program
then closes out by itself. The method for copying a worksheet was to
right click on the tab at the bottom of the worksheet, click on "move or
copy", and then make sure the correct worksheet is highlighted and click
on the empty box "create a copy". A copy of the worksheet is created,
it is adjusted and a renamed using the day formula reflecting the date
and shift the new roster worksheet was created for. The workbook would
be closed and saved to the hard drive. The is no question about room on
the hard drive and no protection or sharing features are untilized. The
workbook refuses to allow us to copy not just the "Master Manpower"
worksheet, but any manpower worksheet that has been created in the
workbook. We have tried creating new workbooks and creating new
manpower worksheets within it, but any workbook created creates the same
error message when we try to copy a worksheet with the workbook. Any
help will be greatly appreciated. For now, are only option is to open
Excel, create a manpower worksheet and save it as a single and separate
Excel file. This will result 365 Excel files for a year per shift
instead of four progressively growing workbooks for each shift.

Thanks,
Bill

[Tom Ogilvy]

Usually this is associated with the "codename" of the worksheet getting up
to 31 or 32 characters in length.

Do alt+F11 to go to the VBE (visual basic editor). You should see the
project explorer on the left hand side. It should have a tree like figure
(kind of like windows explorer) with each sheet listed - showing the name
you can see on the sheet tab and next to it the code name. If you are
seeing names like Sheet1111111111111111111 then this could be your
problem.

Make a copy of your workbook so you are sure you won't lose any data. Then
in the copy, go to the VBE again, click on the Master worksheet name in the
project explorer and look at the properties window (if it is not visible,
hit F4). The first entry is the codename shown as (name). You can try
changing it to a totally new name. Then close and save the workbook. Then
open it and see if you can copy the master sheet.

This article talks about doing it with code, but it can happen manually as
well.
http://support.microsoft.com/support/kb/articles/Q177/6/34.ASP
XL97: Copy Method of Sheets Object Causes Invalid Page Fault

You aren't changing the name with code, so this shouldn't pertian, but it is
one reason I said to work on a copy of the file.
http://support.microsoft.com/support/kb/articles/Q172/5/00.ASP
XL97: Unable to Open File After You Change Code Name of Sheet

Regards,
Tom Ogilvy

Bill Wittmer <wwit...@nospam.home.com> wrote in message
news:3A60ED01...@nospam.home.com...

[Susan Harrelson]

Hi Bill,

After you recover your workbook with Tom Ogilvy's advice, you might want to
think about doing a few things to keep it from happening again. I have
created several worksheets that work very much like what you described.
Here's how I handle them.

Turn the Master Manpower Sheet into a template (.xlt). Instead of copying
the sheet, open the workbook where you need the sheet, click New & open your
template. Move the new sheet into the workbook, rename it, & proceed as
usual.

Monthly, move one month's worth of sheets into a workbook named January
2001, or whatever you want. You still have them collected in a workbook to
minimize clutter, but they aren't garbaging up your active workbook.
Eventually you could even archive these onto CD-R or disks or something, if
necessary.

Although the procedure you describe works OK, it tends to make your file too
large, & the repeated copying seems to introduce file corruption that will
jeopardize your data, as you have discovered.

Susan

"Bill Wittmer" <wwit...@nospam.home.com> wrote in message
news:3A60ED01...@nospam.home.com...

[Bill Wittmer]

Thanks for all your help. I will try Tom's suggestion upon returning to
work Tuesday and hopefully this will resolve my problem. I thank Susan for
her help, I believe her suggestions will improve my scheduling process.

Thank you,
Billl

[Bill Wittmer]

Thanks again to Tom and Susan. I went to back to work Wednesday and tried all
that was suggested. I found that the codenames for all the worksheets had
grown considerably in length. I went into the VB editor and modified the
codenames and was able to copy and save with no problem. To avoid such future
problems, I followed Susan's suggestions and created a template. This appears
to simplify the process of formulating daily manpower worksheets.

Thanks again,
Bill