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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
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,305
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,144,370
Messages
5,723,959
Members
422,529
Latest member
mbilal429

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