calculating sheets

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 "print multiple sheets" function,
however the "calculate the sheets to be printed" 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("Sheet1", "Sheet2", "Sheet3")
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 Vezerides

Thank 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?


"vezerid" <vezerid@act.edu> wrote in message
news:1140526221.535749.127520@g14g2000cwa.googlegroups.com...
> You can do this with VBA, my modifying the line below sh=Array(...)
>
> Sub CalcSheets()
> Dim sh As Variant
> sh = Array("Sheet1", "Sheet2", "Sheet3")
> 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 Vezerides
>

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("MGMT").Cells(i, "A") <> ""
Sheets(Cells(i, "A")).Calculate
i = i + 1
Wend
End Sub

Any better?

Kostis Vezerides

Sorry, one minor change, to avoid possible errors:

Sub CalcSheets()
i = 1
While Sheets("MGMT").Cells(i, "A") <> ""
Sheets(Sheets("MGMT").Cells(i, "A")).Calculate
i = i + 1
Wend
End Sub

It 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?

SPL


"vezerid" <vezerid@act.edu> wrote in message
news:1140540545.857411.269100@g44g2000cwa.googlegroups.com...
> Sorry, one minor change, to avoid possible errors:
>
> Sub CalcSheets()
> i = 1
> While Sheets("MGMT").Cells(i, "A") <> ""
> Sheets(Sheets("MGMT").Cells(i, "A")).Calculate
> i = i + 1
> Wend
> End Sub
>

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("MGMT").Cells(i, "A") <> ""
shname = Sheets("MGMT").Cells(i, "A")
Sheets(shname).Calculate
i = i + 1
Wend
End Sub

Let me know if all goes well

Kostis Vezerides