Use range to move data between worksheets
 

Hi,

Sorry for the crosspost (I've posted to public.excel group), but I
think this a better place to ask. So here it is.

I'm trying to use an array and a range to modify and copy data between
2 worksheets, but when I try to access a sheet that isn't activated,
Excel (2003) gives me a runtime error 1004. Here is my code:

Sub Calc_data()

Dim rowIndex As Integer
Dim colIndex As Integer
Dim iPoints As Integer
Dim iCols As Integer
Dim mult As Double

Dim temp

Dim TempArray()
Dim TheRange As Range

iPoints = Worksheets("Sheet1").Range("numPoints")
iCols = Worksheets("Sheet1").Range("numCols")

ReDim TempArray(1 To iPoints, 1 To iCols)

' If Sheet1 isn't activated before this line, I get the runtime
error 1004 in this next Set
Set TheRange = Worksheets("Sheet1").Range(Cells(9, 3), Cells
(iPoints + 8, iCols + 2))
TempArray = TheRange.Value

For colIndex = 1 To iColunas
If Cells(8, colIndex + 2) = "g" Then
mult = Cells(7, colIndex + 2)
For rowIndex = 1 To iPontos
TempArray(rowIndex, colIndex) = TempArray(rowIndex,
colIndex) * mult
Next rowIndex
End If
Next colIndex

' If Sheet2 isn't activated before this line, I get the runtime
error 1004 in this next Set
Set TheRange = Worksheets("Sheet2").Range(Cells(9, 3), Cells
(iPoints + 8, iCols + 2))
TheRange.Value = TempArray

End Sub

The only workaround I've got, is to use a "Worksheet
("SheetN").Activate", before both Set TheRange, but it seems crude.

BTW, iPoints can be as large as 2881 and iCols can be as large as 50.