Thanks Ryan. I just sent it over.

"Ryan H" wrote:

Is it possible to send me a copy of the workbook so I can try to duplicate
the problem? I can look at it and try to make a suggestion.


--
Cheers,
Ryan


"Ayo" wrote:

I have Workbook.Open and BeforeClose for the workbook level Modules. The
formulas in Worksheets("Goals Tracker").Range("G3:GA712") are pretty much all
SumProduct formula with data derived from another sheet which contains only
data and nothing else.
I know that there is nothing crazy in the code, the only problem is that is
takes forever to complete the Worksheet.Range.calculate line. When I tried:
Worksheets("Goals Tracker").Range("G3:GA12"), it took about a minute to
complete the calculation. Then I tried:
Worksheets("Goals Tracker").Range("G3:GA222"), and it takes more than the 22
minutes I was expecting. As of right now it is still running at 25 mins and
counting

"Ryan H" wrote:

I don't see anything crazy in the code you posted that would be causing this
issue. Is there any macros in the "Goals Tracker" worksheet module? Is so,
can you post those codes?

Do the formulas or values in range Worksheets("Goals
Tracker").Range("G3:GA712") linked to other workbooks? If so, do those
workbooks have worksheet macros?

What about the Workbook Level Modules? Any macros in the SheetCalculate or
SheetChange Events?

--
Cheers,
Ryan


"Ayo" wrote:

There are lots of macros in the work book. I will have to send you the files.
But the only that is causing the problem is inside a Sub for a button on a
Userform:

Private Sub cmdUpdate_Click()
Dim ws As Worksheet, c As Range
Dim BO_Datafile_Name As String, BOReport_lastColumn As String
Dim BOReport_lastRow As Integer, BOPos As Integer
Dim BOReportWS As Worksheet, goalsTrackerWS As Worksheet
Dim rngBOReport As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Workbooks.Open filename:=Me.txtFilePath.Value, UpdateLinks:=0,
ReadOnly:=True
BO_Datafile_Name = ActiveWorkbook.Name

Workbooks(BO_Datafile_Name).Worksheets("Sheet1").S elect
Workbooks(BO_Datafile_Name).Worksheets("Sheet1").C opy
Befo=Workbooks("AAV Daily Reports 2010_ver1.xls").ActiveSheet
'"" CLOSE THE BO DOWNLOAD DATA FILE ""'
Workbooks(BO_Datafile_Name).Activate
Workbooks(BO_Datafile_Name).Close

Workbooks("AAV Daily Reports 2010_ver1.xls").Worksheets("Sheet1").Activate
BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count
Set BOReportWS = Worksheets("BO Download")
ThisWorkbook.Worksheets("Sheet1").Range("A4:AW" & BOReport_lastRow).Copy
ThisWorkbook.Worksheets("BO Download").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("B4").Select
ThisWorkbook.Worksheets("Sheet1").Delete

BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row
BOPos = InStr(1,
BOReportWS.Range("IV3").End(xlToLeft).Address(Colu mnAbsolute:=False), "$",
vbTextCompare)
BOReport_lastColumn =
Left(BOReportWS.Range("IV3").End(xlToLeft).Address (ColumnAbsolute:=False),
BOPos - 1)
Unload Me
Worksheets("BO Download").Select
Range("A2") = "This Report was generated on " & Format(Now, "mmmm d, yyyy
h:mm:ss AMPM") & " (Eastern Time)"
Range("B4").Select
Worksheets("Goals Tracker").Select
Worksheets("Goals Tracker").Range("G3:GA712").Calculate

'"" DELETE BO DOWNLOAD TAB, SAVE THE FILE AND CALCULATE THE WORKSHEET ""'
Dim obj As OLEObject
Dim filename As String, todaysDate As Date
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
'Worksheets("Goals Tracker").Calculate
Worksheets("Goals Tracker").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub

"Ryan H" wrote:

There's no need to select the worksheet in order to do the calculation. Does
the range you reference have a ton of functions in it that refer to macros?
Do you have other macros firing every time a cell in calculated? For
example, if you have code in the worksheet Change or Calculate Events it
could take a while. This may help a little.

Application.ScreenUpdating = False
Worksheets("Goals Tracker").Range("G3:GA712").Calculate
Application.ScreenUpdating = True

Can you post the entire procedure and any other macros that are in the
workbook? It would be more helpful. Hope this helps! If so, let me know,
click "YES" below.
--
Cheers,
Ryan


"Ayo" wrote:

I am having a major problem with these two lines of code in my otherwise
great reporting tool:
Worksheets("Goals Tracker").Select
Worksheets("Goals Tracker").Range("G3:GA712").Calculate

These 2 lines freezes up my computer to the point that I can't use any other
application on my computer. It takes forever (over an hour, the last time I
let it run complete) to calculate one sheet in the workbook. I need to find
out if there is another way to get this done faster.
Any help will be appreaciated.