excel runs slow until closed and reopened
 

I have a macro that copys a lot of formulars then pastes the values.
When it has finished "Calculate" shows in task bar even after recalc
and spread sheet runs slow.
If i save and close spreadsheet it runs fine when reopened untill
macro is run again.

Code I run

LongLine = LongLine _
+ LongerLine

UserForm1.Show vbModeless

UserForm1.Label4 = "Processing Please Wait..."
UserForm1.Label1.BackStyle = fmBackStyleTransparent
UserForm1.count.Value = 1

UserForm1.Repaint


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual 'added 12th April

ActiveSheet.Unprotect ("sidata")

Range("D8:H8").Select
Selection.AutoFilter
Selection.AutoFilter

Range("B9:D446").Select
Range("D446").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

UserForm1.count.Value = 2
UserForm1.Repaint

Range("I9:BW446").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

UserForm1.count.Value = 4
UserForm1.Repaint

Selection.AutoFilter Field:=5, Criteria1:="O-STK"
Range("I449:BW449").Select
Range("BW449").Activate
Application.CutCopyMode = False
Selection.Copy
Range("I11:BW446").Select
Range("I446").Activate
ActiveSheet.Paste

UserForm1.count.Value = 6
UserForm1.Repaint

Selection.AutoFilter Field:=5
Range("B9:D446").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F447").Select

UserForm1.count.Value = 7
UserForm1.Repaint

Selection.AutoFilter Field:=5
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollColumn = 7
Range("G1").Select
ActiveCell.FormulaR1C1 = "FAST MODE - No Update"

ActiveSheet.Protect ("sidata"), DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFiltering:=True

UserForm1.count.Value = 8
UserForm1.Repaint

Application.Calculation = xlCalculationAutomatic 'added 12th April
Application.ScreenUpdating = True
Application.EnableEvents = True

UserForm1.Label1.BackStyle = fmBackStyleOpaque
UserForm1.Label1.BackColor = vbGreen


UserForm1.count.Value = 10
UserForm1.Label4 = "Complete"
UserForm1.Repaint
Application.Wait Now + TimeValue("0:0:04")
UserForm1.count.Value = 0
UserForm1.Repaint
UserForm1.Hide

UserForm1.Hide

'Release memory
Set wSheet = Nothing


End Sub

excel runs slow until closed and reopened
 

You need to unload the user form when you are done with it...
Unload UserForm1
Also, there is no need to hide the form twice.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Sliman"
wrote in message
I have a macro that copys a lot of formulars then pastes the values.
When it has finished "Calculate" shows in task bar even after recalc
and spread sheet runs slow.
If i save and close spreadsheet it runs fine when reopened untill
macro is run again.

Code I run

LongLine = LongLine _
+ LongerLine

UserForm1.Show vbModeless

UserForm1.Label4 = "Processing Please Wait..."
UserForm1.Label1.BackStyle = fmBackStyleTransparent
UserForm1.count.Value = 1

UserForm1.Repaint


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual 'added 12th April

ActiveSheet.Unprotect ("sidata")

Range("D8:H8").Select
Selection.AutoFilter
Selection.AutoFilter

Range("B9:D446").Select
Range("D446").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

UserForm1.count.Value = 2
UserForm1.Repaint

Range("I9:BW446").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

UserForm1.count.Value = 4
UserForm1.Repaint

Selection.AutoFilter Field:=5, Criteria1:="O-STK"
Range("I449:BW449").Select
Range("BW449").Activate
Application.CutCopyMode = False
Selection.Copy
Range("I11:BW446").Select
Range("I446").Activate
ActiveSheet.Paste

UserForm1.count.Value = 6
UserForm1.Repaint

Selection.AutoFilter Field:=5
Range("B9:D446").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F447").Select

UserForm1.count.Value = 7
UserForm1.Repaint

Selection.AutoFilter Field:=5
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollColumn = 7
Range("G1").Select
ActiveCell.FormulaR1C1 = "FAST MODE - No Update"

ActiveSheet.Protect ("sidata"), DrawingObjects:=True,
Contents:=True, Scenarios:=True _
, AllowFiltering:=True

UserForm1.count.Value = 8
UserForm1.Repaint

Application.Calculation = xlCalculationAutomatic 'added 12th April
Application.ScreenUpdating = True
Application.EnableEvents = True

UserForm1.Label1.BackStyle = fmBackStyleOpaque
UserForm1.Label1.BackColor = vbGreen


UserForm1.count.Value = 10
UserForm1.Label4 = "Complete"
UserForm1.Repaint
Application.Wait Now + TimeValue("0:0:04")
UserForm1.count.Value = 0
UserForm1.Repaint
UserForm1.Hide

UserForm1.Hide

'Release memory
Set wSheet = Nothing


End Sub