ExcelBanter - Run Time Issue

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Run Time Issue (https://www.excelbanter.com/excel-programming/299700-run-time-issue.html)

Hello. I am running some code that clears a range of cells on
multiple sheets, then populates the same sheets with data being pulled
from a database-like worksheet. My question is this: Sometimes when
I run the code, it takes 20 seconds. Majority of the time I run the
code it takes 6 minutes! IS there data in a cache or something that
is forcing things to run so slowly? Usually, when I first open the
file and the sheets are already blank, it runs quickly. If I run it
again when the sheets are already populated, it takes forever! So I
thought it was just the sheer size of the file after being populated
that slowed things. BUT, then I selected every sheet, and hit the
home key. And when I ran the code, it ran in 20 seconds! I am SO
confused!! Anyone have any ideas on how to get the code to
consistently run quickly? The code is below. Thanks so much!!

Sub Run_Forecast()
Clear
Pop_Forecast
End Sub

Sub Clear()

Dim sh As Worksheet

Application.ScreenUpdating = False

clrarray = Array("Forecast", "LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup")

For Each sh In ActiveWorkbook.Worksheets(clrarray)
' Sheets("SMLC Cab").Range("A1").Select
' sh.Range("A1").Select
sh.Range("A5:EC500").ClearContents
Next sh
Application.ScreenUpdating = True

End Sub

Sub Pop_Forecast()

Dim shtarray As Sheets
Dim frng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

shtarray.Select
Sheets("LMU").Activate

'Copy Formula
Range("A2:EC2").Copy

Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)
frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

'Consolidate2
Forecast.Range("A5").Consolidate Sources:=Array( _
"'LMU'!R5C5:R500C133" _
, "'Kit'!R5C5:R500C133" _
, "'SMLC'!R5C5:R500C133" _
, "'WLG'!R5C5:R500C133" _
, "'SMLC Cab'!R5C5:R500C133" _
, "'Serv Cab'!R5C5:R500C133" _
, "'Ntwk Kit'!R5C5:R500C133" _
, "'TDAX'!R5C5:R500C133" _
, "'EMS'!R5C5:R500C133" _
, "'SCOUT'!R5C5:R500C133" _
, "'Dir Coup'!R5C5:R500C133" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False

Forecast.Activate

Application.ScreenUpdating = True

End Sub

Hi
try disabling automatic calculation during the run of this macro

--
Regards
Frank Kabel
Frankfurt, Germany

"Steph" schrieb im Newsbeitrag
om...
Hello. I am running some code that clears a range of cells on
multiple sheets, then populates the same sheets with data being
pulled
from a database-like worksheet. My question is this: Sometimes when
I run the code, it takes 20 seconds. Majority of the time I run the
code it takes 6 minutes! IS there data in a cache or something that
is forcing things to run so slowly? Usually, when I first open the
file and the sheets are already blank, it runs quickly. If I run it
again when the sheets are already populated, it takes forever! So I
thought it was just the sheer size of the file after being populated
that slowed things. BUT, then I selected every sheet, and hit the
home key. And when I ran the code, it ran in 20 seconds! I am SO
confused!! Anyone have any ideas on how to get the code to
consistently run quickly? The code is below. Thanks so much!!

Sub Run_Forecast()
Clear
Pop_Forecast
End Sub

Sub Clear()

Dim sh As Worksheet

Application.ScreenUpdating = False

clrarray = Array("Forecast", "LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup")

For Each sh In ActiveWorkbook.Worksheets(clrarray)
' Sheets("SMLC Cab").Range("A1").Select
' sh.Range("A1").Select
sh.Range("A5:EC500").ClearContents
Next sh
Application.ScreenUpdating = True

End Sub

Sub Pop_Forecast()

Dim shtarray As Sheets
Dim frng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

shtarray.Select
Sheets("LMU").Activate

'Copy Formula
Range("A2:EC2").Copy

Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)
frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

'Consolidate2
Forecast.Range("A5").Consolidate Sources:=Array( _
"'LMU'!R5C5:R500C133" _
, "'Kit'!R5C5:R500C133" _
, "'SMLC'!R5C5:R500C133" _
, "'WLG'!R5C5:R500C133" _
, "'SMLC Cab'!R5C5:R500C133" _
, "'Serv Cab'!R5C5:R500C133" _
, "'Ntwk Kit'!R5C5:R500C133" _
, "'TDAX'!R5C5:R500C133" _
, "'EMS'!R5C5:R500C133" _
, "'SCOUT'!R5C5:R500C133" _
, "'Dir Coup'!R5C5:R500C133" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False

Forecast.Activate

Application.ScreenUpdating = True

End Sub

Steph,

Try setting the Application calculation property to xlCalculationManual
before the code, and reset to xlCalculationAutomatic after.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
om...
Hello. I am running some code that clears a range of cells on
multiple sheets, then populates the same sheets with data being pulled
from a database-like worksheet. My question is this: Sometimes when
I run the code, it takes 20 seconds. Majority of the time I run the
code it takes 6 minutes! IS there data in a cache or something that
is forcing things to run so slowly? Usually, when I first open the
file and the sheets are already blank, it runs quickly. If I run it
again when the sheets are already populated, it takes forever! So I
thought it was just the sheer size of the file after being populated
that slowed things. BUT, then I selected every sheet, and hit the
home key. And when I ran the code, it ran in 20 seconds! I am SO
confused!! Anyone have any ideas on how to get the code to
consistently run quickly? The code is below. Thanks so much!!

Sub Run_Forecast()
Clear
Pop_Forecast
End Sub

Sub Clear()

Dim sh As Worksheet

Application.ScreenUpdating = False

clrarray = Array("Forecast", "LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup")

For Each sh In ActiveWorkbook.Worksheets(clrarray)
' Sheets("SMLC Cab").Range("A1").Select
' sh.Range("A1").Select
sh.Range("A5:EC500").ClearContents
Next sh
Application.ScreenUpdating = True

End Sub

Sub Pop_Forecast()

Dim shtarray As Sheets
Dim frng As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

shtarray.Select
Sheets("LMU").Activate

'Copy Formula
Range("A2:EC2").Copy

Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row)
frng.Select
Selection.PasteSpecial Paste:=xlPasteFormulas

'Consolidate2
Forecast.Range("A5").Consolidate Sources:=Array( _
"'LMU'!R5C5:R500C133" _
, "'Kit'!R5C5:R500C133" _
, "'SMLC'!R5C5:R500C133" _
, "'WLG'!R5C5:R500C133" _
, "'SMLC Cab'!R5C5:R500C133" _
, "'Serv Cab'!R5C5:R500C133" _
, "'Ntwk Kit'!R5C5:R500C133" _
, "'TDAX'!R5C5:R500C133" _
, "'EMS'!R5C5:R500C133" _
, "'SCOUT'!R5C5:R500C133" _
, "'Dir Coup'!R5C5:R500C133" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False

Forecast.Activate

Application.ScreenUpdating = True

End Sub