Hello, HELP!

Each time my program runs, it replaces an existing "REPORT" sheet with a new one. The program then writes VBA into the newly created sheet module. This program runs fine the first time (i.e. when there is no pre-existing "REPORT" sheet).

If there is a pre-existing "REPORT" sheet, the program explodes (and I am in the blast radius).

The problem is, I think, that the portion of the program that writes the code into the module sheet does not yet see or recognize that the new "REPORT" sheet exists yet (even though it actually does).

How can I overcome this situation and make it see the new sheet on the fly?

Thanks in advance.

quartz,

Usually, it is solved by starting off with something like

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("REPORT").Delete
.....
'code to add the new REPORT sheet
.......
Application.DisplayAlerts = True

HTH,
Bernie
MS Excel MVP

"quartz" wrote in message
...
Hello, HELP!

Each time my program runs, it replaces an existing "REPORT" sheet
with a new one. The program then writes VBA into the newly created
sheet module. This program runs fine the first time (i.e. when there
is no pre-existing "REPORT" sheet).

If there is a pre-existing "REPORT" sheet, the program explodes (and
I am in the blast radius).

The problem is, I think, that the portion of the program that writes
the code into the module sheet does not yet see or recognize that the
new "REPORT" sheet exists yet (even though it actually does).

How can I overcome this situation and make it see the new sheet on
the fly?

Thanks in advance.

Bernie,

Thanks for responding, but actually my code is already structured similarly to the way you suggested.

I don't get a normal trappable error, I get a fatal error that causes Excel to shut down altogether.

Sometimes my code actually succeeds before it dies, other times, it doesn't write the code into the module when the error occurs.

Any other ideas???

quartz,

Sounds like it is time to:

1) Use Rob Bovey's code cleaner utility

2) If that doesn't work, start from a new workbook and rebuild your
application (unfortunately, it sounds like it is corrupted)

HTH,
Bernie
MS Excel MVP

"quartz" wrote in message
...
Bernie,

Thanks for responding, but actually my code is already structured
similarly to the way you suggested.

I don't get a normal trappable error, I get a fatal error that
causes Excel to shut down altogether.

Sometimes my code actually succeeds before it dies, other times, it
doesn't write the code into the module when the error occurs.

Any other ideas???

Rebuilt the file from scratch. Same behaviour.

Running the code inside the VBE does not generate any error.

Running it as the user would from the App generates the error.