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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
> news:(E-Mail Removed)...
>>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" <(E-Mail Removed)> ha scritto nel messaggio
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> ha scritto nel messaggio
> news:%(E-Mail Removed)...
> > 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