Excel spreadsheet calculations slow

[DIVINEDAR0956]

Can someone help me? How can I get my calculations to run faster? I have the following:

Sub Finalize()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

'Show certain WorkSheets
ShowSheets

RebuildTask
Reb_HRS_Enter
Reb_VENDOR_LIST


' Clean up work sheets
'Delete_Every_Row_With_A_Reference_Error "TASK"
'Delete_Every_Row_With_A_Reference_Error "HRS_ENTER"
'Delete_Every_Row_With_A_Reference_Error "VENDOR_LIST"


'Redo Name Ranges to exclude blank rows
'Redo VENDOR Name Range
RedoNameRange "EXTRACT_VENDOR$", "VENDOR_EXTRACT"

'Redo HOURS Name Range
RedoNameRange "HOURS_EXTRACT", "HOURS_EXTRACT"

'Redo TASK Name Range
RedoNameRange "TASK", "TASK_EXTRACT"

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With


'Need a lock routine

'Select Main Screen again
' Sheets("PROJECT_INFORMATION&quot.Select

'
Extract_Data

'
Extract_Vendor

'Select Main Screen again
Sheets("PROJECT_INFORMATION&quot.Select

'Redo Bid Calculation
Worksheets("PROJECT_INFORMATION&quot.Range("L2&quot.Select
' Selection.Formula = SumBidFormula()

' Hide sheets again
HideSheets

Home

ActiveWorkbook.Save

LockOut

ActiveWorkbook.Close

Application.ScreenUpdating = True
End Sub

This spreadsheet has approximately 150 to 200 worksheets that calculates to fill in several spreadsheets for transporting to access. It seems the more sheets I add the slower the calculation gets.

I tried using the following in the above script:

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

but it doesn't seem to be working.

Does anyone have any ideas on how to make a calculation script run faster in excel regardless of how many sheets are added.

Thank you.

Darlene Sippio
dsippio@comtechsystems.com

 

[vickero007]

How many cells in each sheet have calculations being performed on them?

-Volkoff007

 

[DIVINEDAR0956]

What it does is Row F15:Q19 are rows that are calculated for each spreadsheet and F23:Q38 are calculated for each spreadsheet.

Rows F15:Q19 are calculated to make another worksheet to be transferred into access. This includes all worksheets calculated together to make one worksheet.

Rows F23:Q38 are calculated to also make another worksheet to be transferred into access.

I hope you understand where I coming from.

Anyway each sheet calculates and the final calculation of each is sent to a final worksheet that is transferred into access.

Darlene Sippio
dsippio@comtechsystems.com