C
Chris D
Hi,
Have made some posts already to resolve some problems I have but am stuck
Let me explain:
For a daily reporting system we use a workbook in which the guys in the
field report activities each day. Sheet for day 1 is named "1", day 2 named
"2" etc.
The amount of sheets( days of duration of job) can vary.
In a sheet named " performance" I calculate all the statistics of each
workbook.
Problem is that the amount of sheets vary. I prepared the sheet "
performance" for 60 days, but if a workbook only contains 20 sheets( 20 days)
Excel can't find the sheets from " 21" onwards and I get a popup screen "
update values" which cannot be suppressed( macros are locked out).
My idea to resolve( and need some help):
In the sheet " performance" I prepared for 60 sheets maximum to be in the
workbook. I have disabled all the formulas by replacing the "=" into " ^^^"(
thanks to this forum)
In sheet " performance" column S calculates for day 1( refers to "1"),
columns T for day 2 until column BZ for day 60.
I want to create a macro( which I can start with a button) that executes:
- asks the user for amount of days that are in the workbook and applicable
for calculation
- the macro to calculate the columns that are not applicable for
calculation( suppose there are 10 days in the sheet then the columns AC until
BZ are not applicable)
- the macro to select the columns that are not applicable and delete the
contents of these columns
- the macro to search for cells that contain "^^^" and replace by "="( I
have this script already)
This should resolve the problem of getting popup screen " update values".
Another solution is to just prepare a workbook for 60 days, but the
calculations done by Excel are so intense that the document grows over 7 Mb,
and is useless for the guys in the field because their computers are not as
fast as ours( I can hear: get them new ones...)
I would be very grateful if someone can help me with this.
regards,
Chris D from NL
Have made some posts already to resolve some problems I have but am stuck
Let me explain:
For a daily reporting system we use a workbook in which the guys in the
field report activities each day. Sheet for day 1 is named "1", day 2 named
"2" etc.
The amount of sheets( days of duration of job) can vary.
In a sheet named " performance" I calculate all the statistics of each
workbook.
Problem is that the amount of sheets vary. I prepared the sheet "
performance" for 60 days, but if a workbook only contains 20 sheets( 20 days)
Excel can't find the sheets from " 21" onwards and I get a popup screen "
update values" which cannot be suppressed( macros are locked out).
My idea to resolve( and need some help):
In the sheet " performance" I prepared for 60 sheets maximum to be in the
workbook. I have disabled all the formulas by replacing the "=" into " ^^^"(
thanks to this forum)
In sheet " performance" column S calculates for day 1( refers to "1"),
columns T for day 2 until column BZ for day 60.
I want to create a macro( which I can start with a button) that executes:
- asks the user for amount of days that are in the workbook and applicable
for calculation
- the macro to calculate the columns that are not applicable for
calculation( suppose there are 10 days in the sheet then the columns AC until
BZ are not applicable)
- the macro to select the columns that are not applicable and delete the
contents of these columns
- the macro to search for cells that contain "^^^" and replace by "="( I
have this script already)
This should resolve the problem of getting popup screen " update values".
Another solution is to just prepare a workbook for 60 days, but the
calculations done by Excel are so intense that the document grows over 7 Mb,
and is useless for the guys in the field because their computers are not as
fast as ours( I can hear: get them new ones...)
I would be very grateful if someone can help me with this.
regards,
Chris D from NL