PDA

View Full Version : Sleeper: Quantity Limit on Copying Sheets



gljgreenwald
08-11-2005, 06:30 AM
I have made a excel macro that plots x-y graphs from files the user specifies. I copy the sheet that has the graph template and rename it as a different sheet. I also have the ability to delete these new sheets


Sheets("Pun_Graph").Select
Sheets("Pun_Graph").Copy After:=Sheets(n)


The macro works great -- however, I have found that I get errors with the above code after graphing alot of sheets -- meaning if I try to graph 50 at one time I get an error OR If I graph 10, then delete the 10 and then repeat this, about 10 times I get the same error.

I can close out excel and relaunch and it runs with no issue.

So, my question is: Is there a limitation on how many sheets that can be copied in one session? If so, how many and is there a way I can reset this after each run to avoid this issue?

Thanks,

-gljgreenwald

tommy bak
08-11-2005, 06:55 AM
Hi
If you are running out of memory it's probably because of the autoscaling of font.
This is a well known problem.
In the graph-format-fonts set autoscaling of font off or place something like this in your code


ActiveChart.ChartArea.AutoScaleFont = False

br
Tommy Bak

gljgreenwald
08-11-2005, 07:06 AM
Tommy,

Thanks for the post. I'll give this a try but I don't use any autoscaling of fonts -- I use a template so if I need to make a change I just change the template.

But, I'll give it a try!

thanks,

-gljgreenwald