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.

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" wrote in message
...
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.

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" ha scritto nel messaggio
...
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

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" wrote in message
...
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" ha scritto nel messaggio
...
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

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...

"RB Smissaert" ha scritto nel messaggio
...
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

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.


4N wrote:

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...

"RB Smissaert" ha scritto nel messaggio
...
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

--

Dave Peterson