software

Hi All,

I'm trying to calculate a certain selection of sheets all in one go, the
sheets I want to calculate change on a daily basis. Basically, I want to
pick, say, sheet1, sheet4, sheet7, etc, and then calculate them at the same
time.
I have ASAP on my machine and use the quot;print multiple sheetsquot; function,
however the quot;calculate the sheets to be printedquot; button doesn't seem to
work.
Can anyone help?
You can do this with VBA, my modifying the line below sh=Array(...)

Sub CalcSheets()
Dim sh As Variant
sh = Array(quot;Sheet1quot;, quot;Sheet2quot;, quot;Sheet3quot;)
For i = LBound(sh) To UBound(sh)
Sheets(sh(i)).Calculate
Next i
End Sub

To install:
Alt F11 to go to the VBA IDE
Insert|Module
Paste the code above.

To run:
Alt F8.
Choose CalcSheets
Or you can put a button on a worksheet. When you draw it, Excel will
ask you which macro to assign. Select this macro.

HTH
Kostis VezeridesThank you, but won't I have to change the code each time the sheets I want
to calculate change? For example one week I may want to calculate sheets 3
and 6 and the next week I want to calculate sheets 2, 4, 7 and 10. Is there
a way a can select the relevant sheets and then calculate?quot;vezeridquot; gt; wrote in message oups.com...
gt; You can do this with VBA, my modifying the line below sh=Array(...)
gt;
gt; Sub CalcSheets()
gt; Dim sh As Variant
gt; sh = Array(quot;Sheet1quot;, quot;Sheet2quot;, quot;Sheet3quot;)
gt; For i = LBound(sh) To UBound(sh)
gt; Sheets(sh(i)).Calculate
gt; Next i
gt; End Sub
gt;
gt; To install:
gt; Alt F11 to go to the VBA IDE
gt; Insert|Module
gt; Paste the code above.
gt;
gt; To run:
gt; Alt F8.
gt; Choose CalcSheets
gt; Or you can put a button on a worksheet. When you draw it, Excel will
gt; ask you which macro to assign. Select this macro.
gt;
gt; HTH
gt; Kostis Vezerides
gt;
Create a new management sheet in the same workbook. Select a column,
say A:A, for this purpose. Use cells A1, A2, ... every week to write
the sheets to be calculated. My modified code assumes you will call
this sheet MGMT and that you will start your cells from column A:A.
Then use this variant:

Sub CalcSheets()
i = 1
While Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;) lt;gt; quot;quot;
Sheets(Cells(i, quot;Aquot;)).Calculate
i = i 1
Wend
End Sub

Any better?

Kostis VezeridesSorry, one minor change, to avoid possible errors:

Sub CalcSheets()
i = 1
While Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;) lt;gt; quot;quot;
Sheets(Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;)).Calculate
i = i 1
Wend
End SubIt looks like that is exactly what I want, however I'm having problems
running it. When I click the run button there is an error message which says
Type Mismatch. Is there anything I can do?

SPLquot;vezeridquot; gt; wrote in message oups.com...
gt; Sorry, one minor change, to avoid possible errors:
gt;
gt; Sub CalcSheets()
gt; i = 1
gt; While Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;) lt;gt; quot;quot;
gt; Sheets(Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;)).Calculate
gt; i = i 1
gt; Wend
gt; End Sub
gt;
OK, it gave me the same error message, which I worked around with a
small modification. The following code does not create any error
messages:

Sub CalcSheets()
i = 1
While Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;) lt;gt; quot;quot;
shname = Sheets(quot;MGMTquot;).Cells(i, quot;Aquot;)
Sheets(shname).Calculate
i = i 1
Wend
End Sub

Let me know if all goes well

Kostis Vezerides