D
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
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