Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Sheet Suddenly Runs Slow | Excel Discussion (Misc queries) | |||
Excel runs slow | Excel Worksheet Functions | |||
Excel macro in 2007 runs extremely slow | Excel Discussion (Misc queries) | |||
Excel Runs Slow | Excel Discussion (Misc queries) | |||
2003 Excel VBA on XP runs very slow vs Win/Office 2000 - why? | Excel Programming |