bassnsjp
06-19-2009, 07:33 AM
Im using MS 2003 in a Win XP Pro environment. I cannot connect to my PC that has the code so I must re-type a small example. I've completed a set of macros that perform an extensive comparsion between required and available inventory. Im now in the process of fine tuning it to eliminate how long it takes to run. I inserted time stamps throughout and have narrowed down one process that is taking 30+ seconds. The process is actually just closing a workbook without saving it. Its odd because I copy/close/use other workbooks that are larger and they take only a few seconds to process. :dunno Any suggestions/ideas would be greatly appreciated, thanks in advance.
sub Main
call xyz
end sub
sub xyz
Dim start
Dim finish
' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Delete Shift:=xlup
finish = Timer
Debug.Print "Delete Process: " & finish - start
' This process takes less than a second
start = Timer
Workbooks("AVLwkb").Activate
Cells.Select
Selection.Copy
finish = Timer
Debug.Print "Delete Copy: " & finish - start
' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Paste
finish = Timer
Debug.Print "Delete Paste: " & finish - start
' This process takes 30+ seconds ???? why ????
start = Timer
Application.DisplayAlerts = False
Workbooks("AVLwkb").Close savechanges:=False
Application.DisplayAlerts = True
finish = Timer
Debug.Print "Delete Close: " & finish - start
end sub
sub Main
call xyz
end sub
sub xyz
Dim start
Dim finish
' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Delete Shift:=xlup
finish = Timer
Debug.Print "Delete Process: " & finish - start
' This process takes less than a second
start = Timer
Workbooks("AVLwkb").Activate
Cells.Select
Selection.Copy
finish = Timer
Debug.Print "Delete Copy: " & finish - start
' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Paste
finish = Timer
Debug.Print "Delete Paste: " & finish - start
' This process takes 30+ seconds ???? why ????
start = Timer
Application.DisplayAlerts = False
Workbooks("AVLwkb").Close savechanges:=False
Application.DisplayAlerts = True
finish = Timer
Debug.Print "Delete Close: " & finish - start
end sub