I have a large 2.736 Meg workbook, “WorkBook A”, into
which I enter data from a SHRARED 2.531 Meg workbook via
VBA code similar to the following:

Dim wB_PN2 As Workbook
Dim wS_PN2_PurParts As Worksheet
…
With wS_PN2_PurParts
.Range("QW_PurP_PackTot_Name").Value = arrDataList(1, 3)
End With

With the result that it takes 0.562 seconds to accomplish
the assignment.

We are all using the same Compaq computers:
733Mhz w/ 256Meg RAM with
Windows 2000 Pro Version 5 (5.0.2195 SR-4 Build 2195) and
Excel 2000 (9.0.3821 SR-1)
on a 15 person LAN.

In order to speedup the execution of the Sub-routine
(macro), I wrote a test workbook where I have
A range, rng_Entry, in cells D8:M8
range, rng_Arr, in cells D9:M9
range, rng_Test, in cells E12:E21
and individual ranges, Test_1 - Test_10 in cells E12 – E21

and the following Sub-routine

Sub PushDataIn()
Dim sw As New StopWatch 'Stop Watch Object
Dim TestWS As Worksheet
Dim arrDataList() As Variant
Set TestWS = Worksheets("Sheet1")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
arrDataList = Range("rng_Entry").Value
sw.StartTimer
TestWS.Activate
With TestWS
.Range("Test_1").Value = arrDataList(1, 1)
.Range("Test_2").Value = arrDataList(1, 2)
.Range("Test_3").Value = arrDataList(1, 3)
.Range("Test_4").Value = arrDataList(1, 4)
.Range("Test_5").Value = arrDataList(1, 5)
.Range("Test_6").Value = arrDataList(1, 6)
.Range("Test_7").Value = arrDataList(1, 7)
.Range("Test_8").Value = arrDataList(1, 8)
.Range("Test_9").Value = arrDataList(1, 9)
.Range("Test_10").Value = arrDataList(1, 10)
End With
Debug.Print "" & sw.EndTimer / 1000 & " Seconds"
Application.ScreenUpdating = True
MsgBox "Half-way Home"
Application.ScreenUpdating = False

arrDataList = Range("rng_Arr").Value
'
sw.StartTimer
TestWS.Activate
With TestWS
.Range("rng_Test").Value = Application.Transpose
(arrDataList)
.Calculate
.Range("Box_1").Copy
.Range("Box_1").PasteSpecial Paste:=xlValues
.Range("Box_2").Copy
.Range("Box_2").PasteSpecial Paste:=xlValues
.Range("Box_3").Copy
.Range("Box_3").PasteSpecial Paste:=xlValues
.Range("Box_4").Copy
.Range("Box_4").PasteSpecial Paste:=xlValues
.Range("Box_5").Copy
.Range("Box_5").PasteSpecial Paste:=xlValues
.Range("Box_6").Copy
.Range("Box_6").PasteSpecial Paste:=xlValues
.Range("Box_7").Copy
.Range("Box_7").PasteSpecial Paste:=xlValues
.Range("Box_8").Copy
.Range("Box_8").PasteSpecial Paste:=xlValues
.Range("Box_9").Copy
.Range("Box_9").PasteSpecial Paste:=xlValues
.Range("Box_10").Copy
.Range("Box_10").PasteSpecial Paste:=xlValues
End With
Debug.Print "" & sw.EndTimer / 1000 & " Seconds to
array fill Test cells data"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

When this test workbook is the only workbook open, it
takes between 0 – 0.015 sec. for the first half to execute
and 0.031 sec for the second portion to execute.

If I also open the large “WorkBook A”, the test workbook
takes between 5.703 – 5.765 sec. for the first half to
execute and 1.560 sec. for the second portion to execute.

If I change the name references to cell references, i.e.
E12, the execution time changes to 5.500 – 5.641 sec. for
the first half to execute and 1.140 sec. for the second
portion to execute.

If I have the test workbook and another 604k workbook open
while “WorkBook A” is not open, the test workbook takes
0.015 sec. for the first half to execute and 0.031 sec.
for the second portion to execute. Same as it runs by
itself.
This 604 workbook is mostly sub-routines.
If I have the test workbook and another 2.531 Meg SHARED
workbook (Also see: “Re-Post: Long and Varying Worksheet
Save Times” posted earlier) open while “WorkBook A” is not
open, the test workbook takes between 0 - 0.015 sec. for
the first half to execute and 0.031 sec. for the second
portion to execute. Again, same as it runs by itself.

If I have the test workbook, another 2.531 Meg SHARED
workbook and “WorkBook A” open, the test workbook takes
between 5.797 sec. for the first half to execute and 1.203
sec. for the second portion to execute. Again, same as if
only “WorkBook A” is open with it..

Any ideas why it takes so long to execute the assignments
in and with “WorkBook A”? Too many macros, too many named
ranges (628)? Why does only “WorkBook A” appear to effect
execution time and not other workbooks?

Thanks for your help in advance.