Use range to move data between worksheets

  • Thread starter Thread starter Felipe
  • Start date Start date
F

Felipe

Hi,

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 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.
 
Please don't respond to this thread. I've opened a new one on
microsoft.public.excel.programming, as it is the right place to do it.
 
Back
Top