A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Reload this Page Can't Prevent Recalculation
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can't Prevent Recalculation


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2009, 06:20 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
  
Posts: 21
Default Can't Prevent Recalculation
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
  #2  
Old April 14th, 2009, 06:40 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo[_5_]
external usenet poster
  
Posts: 239
Default Can't Prevent Recalculation
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.



" wrote:

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
  #3  
Old April 14th, 2009, 07:39 PM posted to microsoft.public.excel.worksheet.functions
[email protected]
external usenet poster
  
Posts: 21
Default Can't Prevent Recalculation
On Apr 14, 1:40*pm, Sheeloo just
remove all As... wrote:
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.



" wrote:
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- Hide quoted text -

- Show quoted text -

All of the other workbooks are also set to MANUAL

Don
  #4  
Old April 14th, 2009, 07:59 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo[_5_]
external usenet poster
  
Posts: 239
Default Can't Prevent Recalculation
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..

" wrote:

On Apr 14, 1:40 pm, Sheeloo just
remove all As... wrote:
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.



" wrote:
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- Hide quoted text -

- Show quoted text -

All of the other workbooks are also set to MANUAL

Don
  #5  
Old April 15th, 2009, 12:04 AM posted to microsoft.public.excel.worksheet.functions
Charles Williams
external usenet poster
  
Posts: 235
Default Can't Prevent Recalculation
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

wrote in message
...
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
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:33 PM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.