VBA Move or Copy Worksheets
Posted by Chris on August 31, 2001 11:48 AM
I've written some VB code in Excel 97 that tells Excel what sheets from a large file to copy into a new blank document.
The code works fine on all sheets until it goes to insert the 21st sheet in a looped routine.
On the 21st copying of the sheet Excel will come up with a run time 1004 error stating that the method of selection is incorrect. However there is nothing wrong with the sheet selection as this problem occurs regardless of what order I copy the sheets in. It is always the 21st sheet that comes up with the error. The Loop is written in a way to go above 21 sheets so that isn't the problem either.
If I resume next on errors Excel just closes down!
Has anyone come across or solved this problem in the past?
Help very much appreciated!
Posted by Russell Hauf on August 31, 2001 12:20 PM
I can't be exactly sure of what's going on in your case, but you may want to put some code in to save every 10 sheets or so. So if you are using a counter named intCount, you could do something like this in your loop:
If Count Mod 10 = 0 Then
' Workbooks("NewBook").Save ...
End If
Let me know if this helps,
Russell
Posted by Chris on August 31, 2001 2:26 PM
Thanks Russell
Tried it but unfortunately it didn't help. At least I saved a more recent version before it crashed!
Chris
