B
Bill Martin
I'm stumped.
First some general background: I've got a spreadsheet that I've used for
months. Today it fails on me for no apparent reason. It's got very little
information in it and only a couple of macros. When it fails, Excel locks up,
sends debug info to Msft and closes Excel. Initially I thought it was my
machine at fault and copied the XLS over to another machine and was surprised to
see it fail the same way there.
The specifics: It is one particular macro that fails which simply copies data
from one sheet to another, reformatting a bit as it goes. After a lot of
putzing around inserting MsgBox statements to help debug, I find that the
program enters ok, and executes a few trivial statements properly, and then
fails at the simple statement:
Sheets("ROIC").Select
Hmmm.... Ok, bypassing a bunch of testing that I did, I find that if I copy the
contents of sheet ROIC to another sheet, and change the "Sheets" statement
accordingly it works ok. It only fails when it tries to select the ROIC sheet,
or tries to read/write to it. Likewise it fails if it tries to select or
read/write from the sheet that is the source of the data. But it works ok if I
copy that data off to a new sheet and work from there.
So then I tried renaming the ROIC sheet to something else and then renaming it
back again to ROIC. No help. Somehow Excel does not seem to recognize that the
two sheets exist anymore despite the fact that the VBA editor recognizes them,
and Excel recognizes the sheets with normal formulas -- just not from VBA.
So I copied the sheets to new ones. Deleted the old ones and then renamed the
new ones back to the old names. Everything works great.
I'm totally lost as to why Excel, even after rebooting 50 times today, just can
not recognize the old sheets.
Does this sound familiar to anyone?
Thanks.
Bill
First some general background: I've got a spreadsheet that I've used for
months. Today it fails on me for no apparent reason. It's got very little
information in it and only a couple of macros. When it fails, Excel locks up,
sends debug info to Msft and closes Excel. Initially I thought it was my
machine at fault and copied the XLS over to another machine and was surprised to
see it fail the same way there.
The specifics: It is one particular macro that fails which simply copies data
from one sheet to another, reformatting a bit as it goes. After a lot of
putzing around inserting MsgBox statements to help debug, I find that the
program enters ok, and executes a few trivial statements properly, and then
fails at the simple statement:
Sheets("ROIC").Select
Hmmm.... Ok, bypassing a bunch of testing that I did, I find that if I copy the
contents of sheet ROIC to another sheet, and change the "Sheets" statement
accordingly it works ok. It only fails when it tries to select the ROIC sheet,
or tries to read/write to it. Likewise it fails if it tries to select or
read/write from the sheet that is the source of the data. But it works ok if I
copy that data off to a new sheet and work from there.
So then I tried renaming the ROIC sheet to something else and then renaming it
back again to ROIC. No help. Somehow Excel does not seem to recognize that the
two sheets exist anymore despite the fact that the VBA editor recognizes them,
and Excel recognizes the sheets with normal formulas -- just not from VBA.
So I copied the sheets to new ones. Deleted the old ones and then renamed the
new ones back to the old names. Everything works great.
I'm totally lost as to why Excel, even after rebooting 50 times today, just can
not recognize the old sheets.
Does this sound familiar to anyone?
Thanks.
Bill