time problem
 

Hello,

I am trying to figure out why it takes so long to open workbook,
copy/paste data, save and close workbook. The more often this procedure
is repeated the longer it takes, and the larger the file size becomes,
althought there is not much change in the number of cells containing data

The worksheet has about 75 columns x 1300 rows of data. When the
workbook/worksheet is first created with all the cells populated with
data it takes 1-2 seconds to open,create data, close workbook. In my
application the subsequent procedures to do this now takes a whopping 20
-30 seconds.

I ran the following macro test for a similar size file for 100 repeats
of the procedure. The time increased from 1.8 to about 3.2 secs and
file size increased from about 3 Mb to 3.5 Mb. The file size seems
large to begin with for the amount of data on the worksheet (most of the
cells, though , does have a formula). For some reason test times are
much less than what I am getting in my actual application run.

Any suggestions would be appreciated. I am using Excel 2003


For n = 1 To 100
StartTime = Timer

'Open file
MySymbolPath = SymbolPath & SymbolName & ".xls"
wbName = SymbolName & ".xls"
Workbooks.Open Filename:=MySymbolPath
Set wS = Workbooks(wbName).Worksheets(SymbolName)

'Do Cut, copy,paste

Range("B10:CB1290").Select
Selection.Cut
Range("B1300:CB2580").Select
Selection.Insert Shift:=xlDown

Range("B19:H27").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
Range("I3:CB3").Select
Application.CutCopyMode = False
Selection.Copy

Range("I10:CB18").Select
ActiveSheet.Paste

Range("B10").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

EndTime = Timer
MsgBox Format(EndTime - StartTime, "0.00")
Next n
End Sub