delete method of workbook class failed

patl

Board Regular
Joined
Dec 15, 2005
Messages
123
I have a macro that repeatedly copies worksheets to another workbook, saves that workbook, deletes the worksheets, then copies the worksheets over again. Its works ok, until the 10th or so iteration, then it gives that error. Its the same process happening over and over again so i dont really konw what the cause is. if anyone has some ideas, i would greatly appreciate hearing them. Thanks.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Hard to tell without seeing the code, and knowing some more information about your set-up.

Can you post the code?
 

patl

Board Regular
Joined
Dec 15, 2005
Messages
123
well the code is incredibly long so it probably wouldnt be of value to post the whole thing. This is where is errors tho:

Code:
             while .....
             //Bunch of stuff...
            For Each element In arr
               Sheets(element).Delete
            Next
           ...Loop

I already addressed the obvious problems:
-Correct workbook is active
-It isnt trying to delete all visible worksheets
-Every iteration through the while loop, it does exactly the same thing.

So i dont know what the deal is... Its seems like after a certain number of unrelated delete's, the method stops working.
 

patl

Board Regular
Joined
Dec 15, 2005
Messages
123
well the code is incredibly long so it probably wouldnt be of value to post the whole thing. This is where is errors tho:

Code:
             while .....
             //Bunch of stuff...
            For Each element In arr
               Sheets(element).Delete
            Next
           ...Loop

I already addressed the obvious problems:
-Correct workbook is active
-It isnt trying to delete all visible worksheets
-Every iteration through the while loop, it does exactly the same thing.

So i dont know what the deal is... Its seems like after a certain number of unrelated delete's, the method stops working.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
How exactly are you sure the correct workbook is active?

In fact by not referencing any workbook here VBA will assume you mean the active workbook.
Code:
For Each element In arr
  Sheets(element).Delete
Next
Now that may or may not be the workbook you expect/want.

It would probably be best if you explicitly referenced the workbook.
Code:
For Each element In arr
      Workbooks("Book1.xls").Sheets(element).Delete
Next
Also it might help to know whats actually in arr.

Are the worksheets named with numbers?
 

patl

Board Regular
Joined
Dec 15, 2005
Messages
123
i put in the workbook(name).Sheets(element).Delete

I still have the same issue though. After a number of runs through, it wont do it anymore. But if i stop the code, go to that workbook, and delete the sheet having the same name as element, it lets me do it with no problem. Is there any other code for deleting worksheets?

ps - arr is an array of strings. each string representing a worksheet name. And i have a watch on arr so i know for sure whats in it at all times..
 

Watch MrExcel Video

Forum statistics

Threads
1,114,053
Messages
5,545,740
Members
410,703
Latest member
yaronjoseph
Top