Solved: Problem with For Loop

[newbie2009]

Hi i have an excel workbook with many sheets some of which have table formulas. Pressing F9 takes a long time to calculate. I think VBA can help but i am not sure why the following code would not work (doesnt loop through the next sheets after the first).


Sub Calculate()

Dim MySheet As Worksheet
Dim Counter As Integer


For Each MySheet In ActiveWorkbook.Sheets
ActiveSheet.Calculate
Next MySheet


End Sub

Appreciate any help

[Trevor]

what error(s) are you getting and/or what is happening when you say this isn't working?

[gwkenny]

Hi:

First point with regards to your code: You don't want to say ActiveSheet.Calculate. The code WILL go through all the sheets in the workbook, but only the ACTIVE sheet will be recalculated. You want the code changed to:

For Each MySheet In ActiveWorkbook.Sheets
MySheet.Calculate
Next MySheet

Hopefully you can follow that explanation, if not, please let me know and I will elaborate further.

Second point with regards to calculation: Choosing calculate through vba will not make your spreadsheet calculate faster than F9.

You need to examine your spreadsheet regarding the organization of data and the functions used. Especially recursive functions (array formulas etc...) and for redundant formulas.

[newbie2009]

Thanks. it's working now after i made the changes you suggested