Can't Prevent Recalculation

[donflak]

I have a workbook that has a number of tabs with literally thousands
of formulas and calculations. All of the data is compiled in 7 tables
corresponding to days of the week. I utilize a macro to select rows
3:50000 on each of the 7 tabs and clear contents on a daily basis.
The macro then goes to a network drive, opens a csv file, copies the
data and pastes it in to the corresponding daily tab. It repeats this
process for the entire 7 tabs. The macro concludes by closing each of
the 7 csv files it opened from the network drive, compiles the report
and sends it to the netwrok printer.

Because of some operational changes I have been forced to make changes
to the report to provide much more detailed information. This
required formula and calculation changes. This has slowed the
calculation process down because it now requires multiple data
validations instead of the previous one.

I have the workbook set to MANUAL for recalculation. When the macro
selects and clears contents on the 7 tabs all is okay but as soon as
it goes to the network drive and opens the first csv file, it now
pauses and recalculates the entire workbokk which takes approx 12
mins. After the recalc, it then copies and pastes the data from the
first CSV file and then recalculates again. It continues in this
manner recalculating after pasting the data from each of the CSV
files.

What should be a 15 run is taking well over an hour. As I indicated,
I have the workbook options set to MANUAL and I cannot get it to skip
the unnecessary recalculations.

Any assistance would be greatly appreciated.

The portion of the macro I currently have is

Application.Calculation = xlCalculationManual
Sheets("ROLL-UP").Select
Columns("AH:BG").Select
Selection.EntireColumn.Hidden = False
Sheets("SATURDAY").Visible = True
Sheets("SUNDAY").Visible = True
Sheets("MONDAY").Visible = True
Sheets("TUESDAY").Visible = True
Sheets("WEDNESDAY").Visible = True
Sheets("THURSDAY").Visible = True
Sheets("FRIDAY").Visible = True
Sheets(Array("SATURDAY", "SUNDAY", "MONDAY", "TUESDAY",
"WEDNESDAY", "THURSDAY", _
"FRIDAY")). _
Select
Rows("3:50000").Select
Selection.ClearContents
Workbooks.Open Filename:="T:\FIN\POT-fri.csv"
Workbooks.Open Filename:="T:\FIN\POT-MON.csv"
Workbooks.Open Filename:="T:\FIN\POT-sat.csv"
Workbooks.Open Filename:="T:\FIN\POT-sun.csv"
Workbooks.Open Filename:="T:\FIN\POT-thu.csv"
Workbooks.Open Filename:="T:\FIN\POT-tue.csv"
Workbooks.Open Filename:="T:\FIN\POT-wed.csv"
Windows("POT-sat.csv").Activate
Range("A1:J50000").Select
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("SATURDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("POT-sun.csv").Activate
Range("A1:J50000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("SUNDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("POT-MON.csv").Activate
Range("A1:J50000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("MONDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("POT-tue.csv").Activate
Range("A1:J50000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("TUESDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("POT-wed.csv").Activate
Range("A1:J50000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("WEDNESDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("POT-thu.csv").Activate
Range("A1:J50000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets("THURSDAY").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("ROLL-UP").Select
ActiveWindow.ActivateNext
Application.DisplayAlerts = False
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
Sheets("ROLL-UP").Select
Columns("AI:BG").Select
Selection.EntireColumn.Hidden = True
Windows("OT_Goals_Workbook-Working.xls").Activate
Sheets(Array("SATURDAY", "SUNDAY", "MONDAY", "TUESDAY",
"WEDNESDAY", "THURSDAY", "FRIDAY")).Select Replace:= _
False
Sheets("SATURDAY").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("ROLL-UP").Select
Sheets("Maintenance").Select
Range("A25").Select

 

[Sheeloo]

Write a separate macro which checks the calculation mode after activating
each workbook... I suspect that the calculation mode of the workbook opened
overwrites the manual setting...

 

[donflak]

Write a separate macro which checks the calculation mode after activating
each workbook... I suspect that the calculation mode of the workbook opened
overwrites the manual setting...

--
Pl click the YES button
(if you see it - don''''''''t worry if you don''t),
if this answer was helpful.











- Show quoted text -

All of the other workbooks are also set to MANUAL

Don

 

[Sheeloo]

Just check the mode after a statement like;
Workbooks.Open Filename:="T:\FIN\POT-fri.csv"
You may comment out the actual processing to save time during testing...

Something is setting it back to recalculate..

 

[Charles Williams]

Unfortunately opening a CSV file triggers a recalculation, even in manual
Calc mode: I know its weird but thats the way it is.

You could try one of these suggestions:

- open all the CSV files first, then open the main workbook

OR:
switch all the mega-workbook tabs .EnableCalculation=False until you have
updated the tabs from the CSV files, then switch the tabs
..enablecalculation=True

OR:
convert the CSV files to some other format like .txt or .xls


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com