Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets.copy problem
Ron:
It's not an Excel problem so much as it is an Excel limitation. The number of worksheets allowed in a workbook is dependent on the total volumn of data in the workbook AND the amount of memory on your computer. If you have one or two worksheets crammed full of data, you'll get a lower number of allowable worksheets. Or, you can have a lot of sparsely populated worksheets. In any case, keep in mind that as Excel is manipulating your data, lots of extra memory is being used to keep track of what's going where. That's why you die after 8-10 sheets (you basically run out of memory). BUT, when you close the workbook, Excel cleans up all of the junk which is why you can come back in and get another 8-10 sheets before the whole thing mucks up, again. Unfortunately, doing a SAVE somewhere in the middle of all this doesn't fix the problem - you have to close and re-open the workbook. Programmatically, I would open a second workbook using VBA code and activate it. Then, I would close the original workbook and re-open it, re-activate it, and return control to the user. That way, Excel (itself) is not actually shut-down but the heartburn workbook is 'reset'. Steve in Ohio "Ron McCormick" wrote: I have the following line of code in a model I have been developing. Sheets("Standard data sheet").Copy Befo=Sheets("End data sheet") Essentially my procedure involves copying the standard data sheet to specific section of the workbook by clicking on a button. The standard sheet is renamed with the contents of a range on the worksheet and users are returned to the standard data sheet to enter another set of data. The intent is that the users should be able to repeat this as often as necessary. The procedure works fine for about 8 - 10 copies of the standard sheet, but then on the next copy comes up with a 'run time 1004' error. The procedure will work fine again, for a further 8 - 10 copies, if the model is closed and re-opened again. I have read that there was a bug in earlier versions of Excel relating to this, but it was suggested that it was fixed in more recent versions. This does not appear to be the case. I am using Excel 2002 SP3. Any clarifications or suggested alternative work arounds (ie other than closing and re-opening the file) would be appreciated. -- TIA Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
copy sheets | Excel Worksheet Functions | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Copy Sheets | Excel Programming |