Copy Sheets to RAM and back

[4N]

Hi,

I right now copy a sheet inside a "Variant" matrix using a command like

sheet.Cells(y,x) = matrix(y,x)
and back
matrix(y,x) = sheet.Cells(y,x)

Is there a way to copy the entire table at once or a command to stop
updating the sheet untill I'm done copying to speedup the process?

Thanks.

 

[RB Smissaert]

I take it that your are running your code in a nested loop. You can avoid
that by
assigning the range to the array (and opposite) in one go by doing:

Range(Cells(1), Cells(100, 100)) = Matrix

Matrix = Range(Cells(1), Cells(100, 100))


RBS

 

[4N]

It actually works, but not with every sheet, the Range function in fact
fails in some case.
I tryed activating the other sheets prior to copying them and it looks like
it works.
This method has also a limitation: it doesn't allow copy of portions of the
tables because the table in ram obviously doesn't have the function Range.
Any workaround?

Thanks again.

 

[RB Smissaert]

You don't have to activate the sheets. You can do:

With Sheets(x)
.Range(.Cells(1), .Cells(100, 100)) = Matrix

Matrix = .Range(.Cells(1), .Cells(100, 100))
End With

I don't think you can get a part of an array without
running a loop.


RBS

 

[4N]

I used
With Sheets(2)
.Range(.Cells(1), .Cells(100, 100)) = Matrix
End With

and I ended up having the earlier mentioned error.
I guess the above commands are equivalent to
sheet2.Range(.Cells(1), .Cells(100, 100)) = Matrix

and that doesn't work without activating the sheet.
That isn't a big deal anyway...

 

[Dave Peterson]

Nope. It's equivalent to:

sheet2.Range(sheet2.Cells(1), sheet2.Cells(100, 100)) = Matrix
or
sheets(2).Range(sheets(2).Cells(1), sheets(2).Cells(100, 100)) = Matrix

Each range reference (.range() and .cells()) has to be qualified.