excel runs slow until closed and reopened

S

Sliman

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
 
J

Jim Cone

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help merging two VBA codes 2
Condense code 2
Macro Related to Paste Values 2
Pasting Variable cell info into a macro 1
lenghty code 4
Having difficulty copying and pasting on VBA 3
Copying Solver results of each iteration to a given location in a workbook 1
Code not working as expected 2

Top