Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I move data from a row range to a column range | Excel Discussion (Misc queries) | |||
Move sorted data from one worksheet to multiple worksheets? | Excel Worksheet Functions | |||
Challenge - Help - Move Data to New Worksheets w/totals and timestamps | Excel Programming | |||
Challenge - Help - Move Data to New Worksheets w/totals and timestamps | Excel Programming | |||
HELP REF. NEW DATA ONLY AS i MOVE TO ANY CELL IN THE NEW ROW/RANGE | Charts and Charting in Excel |