Why did you not copy the entire worksheet in one fell swoop?

"mark" wrote:

Hi.

I'm working with a ridiculously large number of formulas.

I manually copied/pasted items on rows C through GK to about 2500 rows.

Each time, it works fine down through row 1045, and then pastes part of row
1046, and quits.

I counted the number of cells that received the paste, and it's 198,300.
Even. I think consistantly.

Does this sound like an Excel limit? Or a system resources limit?

I have another way to do it, but am curious about what the problem is.

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient method
coming along, I think.

I was serious. If you are simply updating from an old sheet to a new one,
then it is easy to just copy the entire sheet in one operation.

You can copy and entire sheet by clicking in the blank cell in the upper
left corner of your sheet, left of column A and above row 1, to select the
entire sheet. Then do a copy and paste.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient method
coming along, I think.

Thought I had better add this. To paste the data to the new sheet. Simply
select Cell A1 then click paste. If you want only the formulas, you can use
the menu and select paste specialformulas.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient method
coming along, I think.

Since your answer seemed to have little relevance to what the OP described,
perhaps that is why he thought it might be sarcastic.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
I was serious. If you are simply updating from an old sheet to a new one,
then it is easy to just copy the entire sheet in one operation.

You can copy and entire sheet by clicking in the blank cell in the upper
left corner of your sheet, left of column A and above row 1, to select the
entire sheet. Then do a copy and paste.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is
that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient
method
coming along, I think.

Could be you are right, Tom. I read his post two or three times trying to
figure out why he was doing individual copy/paste and just thought doing it
en masse might be a little quicker, but apparently he is cherry picking and
pigeon holing, so my suggestion would not be useful.

"Tom Ogilvy" wrote:

Since your answer seemed to have little relevance to what the OP described,
perhaps that is why he thought it might be sarcastic.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
I was serious. If you are simply updating from an old sheet to a new one,
then it is easy to just copy the entire sheet in one operation.

You can copy and entire sheet by clicking in the blank cell in the upper
left corner of your sheet, left of column A and above row 1, to select the
entire sheet. Then do a copy and paste.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is
that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient
method
coming along, I think.

From my reading, it sounds more like he is filling down a row or a few rows
to many rows - so the sheet to copy doesn't exist. Then again, I could be
misreading it.

--
Regards,
Tom Ogilvy

"JLGWhiz" wrote in message
...
Could be you are right, Tom. I read his post two or three times trying to
figure out why he was doing individual copy/paste and just thought doing
it
en masse might be a little quicker, but apparently he is cherry picking
and
pigeon holing, so my suggestion would not be useful.

"Tom Ogilvy" wrote:

Since your answer seemed to have little relevance to what the OP
described,
perhaps that is why he thought it might be sarcastic.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
I was serious. If you are simply updating from an old sheet to a new
one,
then it is easy to just copy the entire sheet in one operation.

You can copy and entire sheet by clicking in the blank cell in the
upper
left corner of your sheet, left of column A and above row 1, to select
the
entire sheet. Then do a copy and paste.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it,
is
that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient
method
coming along, I think.

Sorry I didn't get back to this until today.

Tom is right, I was copying from one row of data, cells A8:GK8 , down to the
next couple of thousand rows, on that same sheet.

It seemed to hit some type of limit after properly adding 198,300 pasted
cells.

From then on, I could paste in more simple formulas, like =sum(), but no
more of the Array formulas could be posted.

I looked at the specifications, and see that there's a limit on the number
of arrays you can have in a worksheet, but I'm not sure if that's the
relevant issue here, or not. I only have about 8 or so arrays referenced in
all of the array formulas (not all 8 in one formula, but, all of the
individual array formulas reference about 8, in total).

That method of processing takes way too long, anyway... problem with
figuring out the answers to the array formulas, on a couple of thousand rows,
from data arrays that are tens of thousands of rows.

I put it into a loop that used auto-filter to access only the data relevant
for a row of data, have the array formulas evaluate based upon that much
smaller chunk of data, copy/paste-special values, and then cycle through to
the data relevant for the next row.

It works a lot faster, since it doesn't have to look at anywhere near as
many rows to figure out the answer.

But I would still like to know what limit I was hitting in the mass copy.

Thanks,
Mark

"JLGWhiz" wrote:

Thought I had better add this. To paste the data to the new sheet. Simply
select Cell A1 then click paste. If you want only the formulas, you can use
the menu and select paste specialformulas.

"mark" wrote:

Why did you not copy the entire worksheet in one fell swoop?


Well, the trouble with your answer, or at least my perception of it, is that
I don't know whether it is serious, or sarcastic.

if serious, please explain.

if sarcastic, I'll be fine on my own... have my other more efficient method
coming along, I think.