Macro error with message 400

[Bill Richardson]

I have run into an error on a workbook I created in Excel 97 and has run
with no problems before. The workbook is for planning and tracking
training programs for athletes. It has a yearly summary sheet and a
model sheet for a weeks activities. The macro copies the model sheet 51
times for the remaining weeks of the year and then copies information
from the yearly summary into each week sheet.

When I run the macro it creates weeks up to 43 then halts with an error
box with just the number 400, nothing else. If I delete the new sheets
and try to run the macro again it halts immediately with the same
message. If I exit out of Excel and go back in the same process repeats
itself.

Does anyone have any ideas about the cause of the problem and possible
solutions.

TIA

--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]

 

[Kevin Stecyk]

Hi Bill,

Please post your macro with as much description as possible.

I am curious, so if you want to e-mail your workbook to me, I would be
interested in looking at it. Even if you do e-mail it to me, please post
your code here for others to review and comment.

My email is my last name at yahoo dot com.

Best regards,
Kevin H. Stecyk

Calgary

 

[Kevin Stecyk]

Hi Bill,

I can replicate your problem. I will see if I can find where the program
has difficulties.

I encourage others to participate as well. Most who participate in this
newsgroup are better at VBA than I am. But I will see what I can discover.

Regards,
Kevin





Hi Kevin:

Here is the macro.

Thanks

Bill

Hi Bill,

Please post your macro with as much description as possible.

I am curious, so if you want to e-mail your workbook to me, I would be
interested in looking at it. Even if you do e-mail it to me, please post
your code here for others to review and comment.

My email is my last name at yahoo dot com.

Best regards,
Kevin H. Stecyk

Calgary



I have run into an error on a workbook I created in Excel 97 and has run
with no problems before. The workbook is for planning and tracking
training programs for athletes. It has a yearly summary sheet and a
model sheet for a weeks activities. The macro copies the model sheet 51
times for the remaining weeks of the year and then copies information
from the yearly summary into each week sheet.

When I run the macro it creates weeks up to 43 then halts with an error
box with just the number 400, nothing else. If I delete the new sheets
and try to run the macro again it halts immediately with the same
message. If I exit out of Excel and go back in the same process repeats
itself.

Does anyone have any ideas about the cause of the problem and possible
solutions.

TIA

--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]








--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]

 

[Kevin Stecyk]

Bill and Others,

Here's what I have learned.

1.) I know where the problem occurs, but I do know why or how to solve it.
2.) Bill had his macro in the ThisWorkbook. Here he had an error message "400" as he indicated. I moved his macro to a separate module and got the following error message.

Run-time error '1004':
Copy method of worksheet class failed.

I should note that the error occurred in exactly the same location in the code.

Here is a snippet of Bill's code:

For Counter = 2 To 52
Sheets("Week 1").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
Sheets(ActiveWorkbook.Sheets.Count).Name = "Week " + Str(Counter)
Range("A2").Select
ActiveCell.FormulaR1C1 = Trim(Str(Counter))
Range("A5").Select
ActiveCell.FormulaR1C1 = StartDate + (7 * Counter)
Calculate

Range("B8:B11").Clear


I put in debug.print statements to locate the error. So the modified code looks like the following:

For Counter = 2 To 52


Debug.Print "C" & Counter & " 30"
Debug.Print "Sheets Count: " & ActiveWorkbook.Sheets.Count

Sheets("Week 1").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)

Debug.Print "C" & Counter & " 40"

Sheets(ActiveWorkbook.Sheets.Count).Name = "Week " + Str(Counter)

Debug.Print "C" & Counter & " 50"
Range("A2").Select
ActiveCell.FormulaR1C1 = Trim(Str(Counter))
Range("A5").Select
ActiveCell.FormulaR1C1 = StartDate + (7 * Counter)
Calculate


In the immediate window, I see the following:

preceding stuff
....
C45 30
Sheets Count: 47

So as Bill mentioned, the macro dies just after the 44th month. It just began the 45th iteration ("C45"). The sheet count of 47 is correct as there are 3 other sheets in addition to the 44 existing month sheets.

So I don't know why the macro fails. I just know where it fails.

It is late, but if someone wants a copy of my "debug.print" file, I can send it to them by e-mail tomorrow. But given what I have written, you know where to look for the error. Thus, you can trap and analyze it how you see fit.

I look forward to seeing how this problem is debugged.

Best regards,
Kevin



Hi Kevin:

Here is the macro.

Thanks

Bill

Hi Bill,

Please post your macro with as much description as possible.

I am curious, so if you want to e-mail your workbook to me, I would be
interested in looking at it. Even if you do e-mail it to me, please post
your code here for others to review and comment.

My email is my last name at yahoo dot com.

Best regards,
Kevin H. Stecyk

Calgary


I have run into an error on a workbook I created in Excel 97 and has run
with no problems before. The workbook is for planning and tracking
training programs for athletes. It has a yearly summary sheet and a
model sheet for a weeks activities. The macro copies the model sheet 51
times for the remaining weeks of the year and then copies information
from the yearly summary into each week sheet.

When I run the macro it creates weeks up to 43 then halts with an error
box with just the number 400, nothing else. If I delete the new sheets
and try to run the macro again it halts immediately with the same
message. If I exit out of Excel and go back in the same process repeats
itself.

Does anyone have any ideas about the cause of the problem and possible
solutions.

TIA

--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]






--
Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]

 

[Tom Ogilvy]

If you go into the VBE and look at the sheets do they have names like

Sheet1111111111111111111

If so, this is the source of your error. Xl97 has a bug where it increments
the codename of the sheet with the above type structure and eventually it
exceeds the allowable length for a codename.

This happens very quickly when you copy a copy - in your case, you say you
are always copying the original, so the progression is not quite as fast.

Try giving your original sheet a code name like A or as short a name as
possible and maybe you will make it to 51.

 

[Tom Ogilvy]

Here is some additional.
http://support.microsoft.com/default.aspx?scid=kb;en-us;177634&Product=xlw97
XL97: Copy Method of Sheets Object Causes Invalid Page Fault

 

[Kevin Stecyk]

Hi Tom,

From the reference you provided, it states, "Microsoft has confirmed this to
be a problem in Microsoft Excel 97. This problem no longer occurs in
Microsoft Excel 2000. "

I am using XL 2000, and the problem still exists.

Regards,
Kevin

 

[Kevin Stecyk]

Hi Tom,

No, the sheets are properly named all the way through.

For example, just before the macro dies, the last sheet added was "Sheet47
(Week 44)" which is as it ought to be.

I have indicated in my responses where the error occurs, though I don't know
why or how to solve.

Also, I am using XL 2000.

Regards,
Kevin

 

[Tom Ogilvy]

Bill says: Excel 97

I am aware this isn't a problem in xl2000.

If it is something more abstract, then I can't say what it is.

 

[Kevin Stecyk]

Hi Tom,

Thank you for your reply.

Yes, you are correct Bill is using XL 97. But the error still occurs using
XL 2000 (the version that I am using). So I don't believe the error you
referenced is the problem because if it were the problem, then it ought to
have run correctly for me.

Also, the sheets are properly numbered. I looked at the sheet numbering
yesterday and it seemed fine. After I saw your note, I double checked.

This problem appears to be a stinker. I have spoken with Myrna Larson, and
I think she will help me solve this problem, or perhaps more accurately,
Myrna will crack the case and let me know what she did. This problem will
be a good learning opportunity.

This is a weird problem, because the program does appear to mysteriously die
for no apparent reason. I did some of my debugging and didn't find anything
wrong. But I am not a VBA wizard, so I am keen to see what Myrna is able to
spot.

Again, thank you for your reply.

Best regards,
Kevin

 

[Bill Richardson]

Hi Kevin/Tom:

Just to clarify, I created the workbook in 97 but I have run it in 2000
and 2002 as well. I'm using 2002 right now.

Thanks for the assistance.

Bill says: Excel 97

I am aware this isn't a problem in xl2000.

If it is something more abstract, then I can't say what it is.

--

Bill

************************************************************
Bill Richardson
Systems Manager
Sport Technology Research Centre
Faculty of Kinesiology
The University of Calgary
2500 University Dr. N.W.
Calgary, AB
Canada T2N 1N4
Phone: (403) 220-5194
Fax: (403) 284-2098
Internet:[email protected]

 

[Kevin Stecyk]

Hi Bill,

I am glad you got your macro to work.

Myrna Larson, who is a frequent contributor to this newsgroup and who has
excellent XL VBA skills, was unable to solve your issue as well. I know she
streamlined your code, and moved your code to a separate module as opposed
to leaving it in "ThisWorkbook". All her efforts were to no avail.

Tom pointed us towards the problem all along, though the MS knowledge Base
Article led us astray with the claim that the problem had been addressed
with XL 2000. Your example proved that claim to be false.

I don't think there is much to do at this point, except that you might want
to forward your spreadsheet to MSFT to let them know that their problem is
NOT fixed in XL 2000 or XL 2002, and possibly XL 2003.

In any event, I am glad that your problem has been addressed.

Best regards,
Kevin

 

[Myrna Larson]

I was going to try that next (add a blank sheet, copy the contents and formatting of template
sheet to it), but didn't get around to it.

BTW, did you check whether the Button Click event code is copied to the new module? I suspect it
isn't. In that case, you'll have to modify your macro to create the button's code in the new
sheet module.

I wonder if you could set up your "Week 1" sheet as a template, then add new sheets based on
this template.

As for the "why", it's a bug in Excel! Who knows where/what they fouled up.....

 

[Myrna Larson]

Kevin asked about how to use the template idea, and I discovered that it isn't going to work, at
least not easily. Workbooks.Add has a Template argument. I was thinking that Sheets.Add did
also, but it doesn't. My goof. I should have checked before posting!

To make the template idea work, you would have to

1. Set up the template by putting a copy of the Week 1 sheet in a separate book and saving it as
a template, say Week1.xlt, in the directory with the workbook. This would have to be done only
once.

2. When the macro runs, locate the XLSTART directory (Application.StartupPath gives you this).

3. Rename any existing Sheet.xlt in the XLSTART directory, say to x.xlt

4. Copy Week1.xlt to that directory and name it Sheet.xlt

5. Run the macro to add the worksheets

6. Kill xlstart\sheet.xlt

7. Rename xlstart\x.xlt as Sheet.xlt

That seems very cumbersome to me -- and all because of a MS bug!!!

I wonder if the simplest approach isn't just to set up the workbook with all 52 sheets from the
start. Then the macro would need only to loop through the "week" sheets, make the changes that
it's already doing and in addition clear the contents of $G$28:$G$60 on each sheet.

 

[Dave Peterson]

I haven't followed the thread, but this worked ok for me in xl2002:

Sheets.Add Type:="c:\my documents\excel\book1.xls"
as did:
Sheets.Add Type:="c:\my documents\excel\book4.xlt"

(I think your memory is pretty darn good!)

(And I think it does in earlier versions, too. But if it didn't, couldn't Keven
add a new workbook based on that template workbook and just move the sheet into
its rightful spot--again from someone not following the thread--so I could have
missed something important.)

 

[Myrna Larson]

Bill wants to add 51 worksheets. The code is crashing on the Copy statement after several sheets
have been added. It isn't the original bug re the name of the event module for the worksheet
that surfaced in XL97 and was supposedly fixed in XL2000.

His original code crashed for me after copying the sheet 44 times. This was with XL2002. I
modified his code to optimize it a bit, and after doing that it crashed on the first copy!

 

[Myrna Larson]

Interesting approach. Help describes the Type argument as a number, so I'm surprised a file path
works. But if it does, so much the better!

 

[Frankie Bones]

Yes, this is a nasty one.

I had a similar problem like this before.
Try this.

1. Make a copy of the excel doc.,

2. Edit the macro in the copied doc. so that it opens your origina
doc.

Add Line: Workbooks.Open ("YourOriginalDoc.xls")


3. Then add a control loop that will save, close and re-open th
original doc after 40 copies have been preformed:

ActiveWorkbook.Close SaveChanges:=True _
Set ActiveWorkbook = Nothing _
Workbooks.Open ("YourExcelDoc.xls")

Let me know if you if this isn't clear