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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,302
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
76,302
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..
 

Forum statistics

Threads
1,140,916
Messages
5,703,161
Members
421,278
Latest member
16cwilliams

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top