Hi All,

I apologise in advance for the lengthy mail.
I have a major file that:
1. Imports data from SQL Server (between 500 and 30 000 rows depending
on certain criteria) and places this on my first sheet in columns A to
AD
2. Imports additional data (same amount of rows as above) and places
this into six additional sheets.These sheets are used to do
calculations (in one sheet up to column FE!)
3. Places formulas in the first sheet from columns AE to CQ for each
row of data

After retrieving the data the formulas in the six calculation sheets
are populated using something similar to the sub below:
Application.StatusBar = "Preparing to do calculations"
'Area Calcs
Worksheets("AreaCalcs").Activate
Worksheets("AreaCalcs").Range("A3").Select
Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Clear
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Range("A2:A" & intRowCount + 1).Select
ActiveSheet.Paste
With Application
.CutCopyMode = False
.Calculate
.StatusBar = "Area calculations complete"
End With
ActiveWorkbook.Names.Add Name:="tblAreaCalcs", _
RefersToR1C1:="='AreaCalcs'!R1C1:R" & intRowCount + 1 &
"C121"
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

As can be seen, the formulas "wait" in the second row and are copied
down...

After this is complete I then place formulas in columns AE to CQ on the
first sheet using the method illustrated he

' Range("AE2").Select
' ActiveCell.FormulaR1C1 = _
'
"=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
' Selection.Copy
' Range(ActiveCell, ActiveCell(intCount, 1)).Select
' ActiveSheet.Paste
' Application.Calculate
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
' :=False, Transpose:=False

which I subsequently replaced with the code here under after reading
that selecting and activating slows processing down

Set rng = Range("AE2:AE" & intRowCount + 1)
rng =
"=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
Application.Calculate
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

(The liberal use of Paste Special Values is to decrease the save size
of the file which sometimes reaches 300MB!)
This whole process takes up to a half hour for the larger data sets,
and whereas the six sheets seeem to go quickly the populating of the
columns in the first sheet seems soooo slow.Without using a stopwatch,
I am convinced that the second code is slower, or if better, only
marginally....My short questions for such a long story are...am I on
the right track? Is there quicker/more efficient ways?