delete method of workbook class failed

[patl]

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.

 

[Norie]

Hard to tell without seeing the code, and knowing some more information about your set-up.

Can you post the code?

 

[patl]

well the code is incredibly long so it probably wouldnt be of value to post the whole thing. This is where is errors tho:

             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]

well the code is incredibly long so it probably wouldnt be of value to post the whole thing. This is where is errors tho:

             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]

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.

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.

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]

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..