Run out of resources .... loop to new tabs
 

Thanks Ron but as I go through the list and tabs what I am cutting and
pasting is getting smaller and smaller, some how I think Excel is keeping
track of something that I do not know understand.


I have this long list of records and I separate it into new tabs by stepping
down through the rows checking for a different value. When I get a different
value, I cut and paste the data from that point down to a new tab and then
start again on that new tab till Im done. I re-use the same named range for
the cut and paste each iteration.

It worked fine for awhile but now I have a huge file and it is running out
of resources. Is there a way to 'clear' the resources each iteration or
something to that effect? I seem to remember stack issues but do not know how
to go about it in VBA.

Thanks

Run out of resources .... loop to new tabs
 

Hi Doug,
You should stick with your original thread, so that Ron and others can more
easily follow the entire problem from beginning to end. Very important for people
who search newsgroup archives to previous questions/answers.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

I presume that means until you crash again.
Try clearing your temporary files in Internet Explorer.
http://www.mvps.org/dmcritchie/excel/slowresp.htm

I suspect from your description you are changing the active cell, which
is not a good thing to do.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Doug929" wrote in message ...
Thanks Ron but as I go through the list and tabs what I am cutting and
pasting is getting smaller and smaller, some how I think Excel is keeping
track of something that I do not know understand.


I have this long list of records and I separate it into new tabs by stepping
down through the rows checking for a different value. When I get a different
value, I cut and paste the data from that point down to a new tab and then
start again on that new tab till I'm done. I re-use the same named range for
the cut and paste each iteration.

It worked fine for awhile but now I have a huge file and it is running out
of resources. Is there a way to 'clear' the resources each iteration or
something to that effect? I seem to remember stack issues but do not know how
to go about it in VBA.

Thanks

Run out of resources .... loop to new tabs
 

Thanks David, sorry about the thread thing. I think the 'lastcell' info is
the answer but I can not make it work, Is something different in 2003? I am
looking at the code;

Sub Reset_lastcell()

Dim x As Long
x = ActiveWorksheet.UsedRange.Rows.Count
End Sub


and get the error

run time error 424
object required

As I described I have 47000 rows, I step down through and select say 46500
and paste them on a new tab and then start on that tab again, I think your on
to the problem because if I go and look at the previous sheet the last cell
is down there at 47000 instead of 500. What am I missing to reset the last
cell? Thanks for the reply.


"David McRitchie" wrote:

Hi Doug,
You should stick with your original thread, so that Ron and others can more
easily follow the entire problem from beginning to end. Very important for people
who search newsgroup archives to previous questions/answers.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

I presume that means until you crash again.
Try clearing your temporary files in Internet Explorer.
http://www.mvps.org/dmcritchie/excel/slowresp.htm

I suspect from your description you are changing the active cell, which
is not a good thing to do.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Doug929" wrote in message ...
Thanks Ron but as I go through the list and tabs what I am cutting and
pasting is getting smaller and smaller, some how I think Excel is keeping
track of something that I do not know understand.


I have this long list of records and I separate it into new tabs by stepping
down through the rows checking for a different value. When I get a different
value, I cut and paste the data from that point down to a new tab and then
start again on that new tab till I'm done. I re-use the same named range for
the cut and paste each iteration.

It worked fine for awhile but now I have a huge file and it is running out
of resources. Is there a way to 'clear' the resources each iteration or
something to that effect? I seem to remember stack issues but do not know how
to go about it in VBA.

Thanks

Run out of resources .... loop to new tabs
 

Sometimes it works, sometimes it doesn't. You will have to save
before it is becomes effective in any case. Try Debra's
Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
http://www.contextures.com/xlfaqApp.html#Unused

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Doug929" wrote in message ...
Thanks David, sorry about the thread thing. I think the 'lastcell' info is
the answer but I can not make it work, Is something different in 2003? I am
looking at the code;

Sub Reset_lastcell()

Dim x As Long
x = ActiveWorksheet.UsedRange.Rows.Count
End Sub


and get the error

run time error 424
object required

As I described I have 47000 rows, I step down through and select say 46500
and paste them on a new tab and then start on that tab again, I think your on
to the problem because if I go and look at the previous sheet the last cell
is down there at 47000 instead of 500. What am I missing to reset the last
cell? Thanks for the reply.


"David McRitchie" wrote:

Hi Doug,
You should stick with your original thread, so that Ron and others can more
easily follow the entire problem from beginning to end. Very important for people
who search newsgroup archives to previous questions/answers.
http://www.mvps.org/dmcritchie/excel/xlnews.htm

I presume that means until you crash again.
Try clearing your temporary files in Internet Explorer.
http://www.mvps.org/dmcritchie/excel/slowresp.htm

I suspect from your description you are changing the active cell, which
is not a good thing to do.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Doug929" wrote in message ...
Thanks Ron but as I go through the list and tabs what I am cutting and
pasting is getting smaller and smaller, some how I think Excel is keeping
track of something that I do not know understand.


I have this long list of records and I separate it into new tabs by stepping
down through the rows checking for a different value. When I get a different
value, I cut and paste the data from that point down to a new tab and then
start again on that new tab till I'm done. I re-use the same named range for
the cut and paste each iteration.

It worked fine for awhile but now I have a huge file and it is running out
of resources. Is there a way to 'clear' the resources each iteration or
something to that effect? I seem to remember stack issues but do not know how
to go about it in VBA.

Thanks