asked on

Excel VBA Worksheet.Delete fails

Help, please. I have been beating my head against this problem for 2 weeks and cannot figure out what I am doing wrong.

Using VBA, I'm automating Excel from within Word. At one point, my macro does this:
1. open up a Master Excel workbook (an .xlsm) containing pre-configured tables and graphs
2. create a new workbook (xlbook)
3. copy one or more worksheets from the Master to xlbook (based on user selections)
4. close Master book
5. remove unneeded worksheets from xlbook
6. Save xlbook as an .xlsm

I'm getting hung up on step #5. For some reason, i cannot delete the default sheets. I've tried a half dozen methods to iterate through the worksheets and delete these, but nothing seems to be working. the simplest approach would be:

xlapp.DisplayAlerts = False
For Each mysheet In xlbook.Worksheets
    If mysheet.Name = "Sheet1" Or mysheet.Name = "Sheet2" Or mysheet.Name = "Sheet3" Then
        mysheet.Delete
        MsgBox xlbook.Worksheets.Count
    End If
Next mysheet

Open in new window

Even when DisplayAlerts is set to True, I get no error message on mysheet.Delete, so it appears to be deleting each sheet, but the MsgBox keeps giving me the same sheet count each time,meaning the delete is not working. And when I open the new workbook in Excel, no surprise it still contains Sheet1, Sheet2, and Sheet3.

This is frustrating because it has worked perfectly in the past. The only change I am making is the master book is now an .xlsm, but I don't save the new book as .xlsm until step #6, so I don't see how that can be the issue.

Can anybody spot what I am doing wrong? Thanks!

Martin Liss

Try it without the quotes around the sheet names.

Martin Liss

If those aren't the names of the tabs then use the tab names (with quotes).

Martin Liss

Another possibility. Is mysheet defined as a Worksheet? If not it should be.

Subodh Tiwari (Neeraj)

Are you sure you are referring to correct workbook i.e. xlbook? How and where do you create xlbook? What's the full code?

Fabrice Lambert

Mind uploading your workbooks ?

Also, why do you copy some worksheets from the master ?
It will be easyer to:
1) Copy the master file.
2) Open the copy
4) Remove uneeded sheets.
5) Save the copy

Bryce Bassett

ASKER

Thanks for your thoughts, gentlemen. But this still is not working. I'm simply trying to remove the default sheets that are part of a newly opened workbook. That's why I put the names in quotes. If I remove the quotes it thinks those are variables so it doesn't find them and deletes nothing. mysheet and xlbook are properly defined, and I have hundreds of lines of code interacting with those objects correctly before I get to this final cleanup section. That's why it's such a mystery. It does everything up to this point but fails to delete.

Sorry, I cannot share the workbooks or any more of my code as it is client proprietary. I thought about a subtractive method as Fabrice suggests, but I would have to completely re-write my code, which has worked without problems for 2 years now. Besides there's a lot more going like copying some of the sheets multiple times if needed into the new book, with different names. Beside which, if I can't delete these 3 built-in sheets, why should I think I will be any more successful deleting 25 sheets?

I restored my old code just now, with the Master as an xlsx, and it works fine. It deletes those sheets no problem using the same exact code snippet. Still looking for an answer.

Thanks

Wayne Taylor (webtubbs)

Instead of trying to delete, can you try the below to see if it is interacting with the sheets at all?

xlapp.DisplayAlerts = False
For Each mysheet In xlbook.Worksheets
    If mysheet.Name = "Sheet1" Or mysheet.Name = "Sheet2" Or mysheet.Name = "Sheet3" Then
        mysheet.Name = "XXX_" & mysheet.Name
        MsgBox xlbook.Worksheets.Count
    End If
Next mysheet

Open in new window

If it is interacting with the worksheets correctly, the sheets should be renamed "XXX_Sheet1", etc.

Also, do you have any error capturing any where, or error disabling with "On Error Resume Next"?

Martin Liss

With 2 workbooks open and the target workbook being Workbook(2), this works,

Sub del()

Dim xlbook As Workbook
Dim mysheet As Worksheet

Set xlbook = Workbooks(2)
Application.DisplayAlerts = False
For Each mysheet In xlbook.Worksheets
    If mysheet.Name = "Sheet1" Or mysheet.Name = "Sheet2" Or mysheet.Name = "Sheet3" Then
        mysheet.Delete
        MsgBox xlbook.Worksheets.Count
    End If
Next mysheet

End Sub

Open in new window

Bryce Bassett

ASKER

That's a good test. Yes, it is interacting with the sheets. See image below.

But if I put the delete command right after that, it still fails to delete.

For Each mysheet In xlbook.Worksheets
    If mysheet.Name = "Sheet1" Or mysheet.Name = "Sheet2" Or mysheet.Name = "Sheet3" Then
        mysheet.Name = "XXX_" & mysheet.Name
        mysheet.Delete
    End If
Next mysheet

Open in new window

I don't have any error trapping at this point in my code.

Bryce Bassett

ASKER

Well, that works if there's a fourth sheet, because it won't let you delete every sheet in an open workbook.

But as I mentioned, my code worked fine when I was dealing with an .xlsx as the Master. Now that I'm using an .xlsm, I'm seeing this problem. The weird thing is, why would the code care? The sheets I have copied from the Master into the new book bring with them some macro code and named ranges which weren't there before with the .xlsx. Could that have anything to do with it?

Wayne Taylor (webtubbs)

Are you able to delete the worksheets manually?

Bryce Bassett

ASKER

Yes, I can delete them manually.

I'm jumping offline for the night and won't be able to look at this again until Monday evening, but if anybody has any further ideas, I'll check back then. Thanks, all.

Fabrice Lambert

I thought about a subtractive method as Fabrice suggests, but I would have to completely re-write my code, which has worked without problems for 2 years now.

Maybe you don't want to hear that:
That fact that it work doesn't mean it is right !

Guess your functions break the SRP and do too many things at once, ehence the difficulties to fix it.

Post your code, maybe we'll be able to figure something out, instead of working on a small part that doesn't fit anywhere due to unknown dependencies.

Also, does your master have any reason to support macros ?
If no, leave it alone.
Else, elaborate.

Martin Liss

But if I put the delete command right after that, it still fails to delete.

Are you trying to rename the sheets rather than to delete them? If so then remove line 4 because otherwise line 4 will delete the renamed sheet, and since there must be at least 1 worksheet in every workbook, and if there are only Sheet1, 2 and 3 in the workbook, you'll get an error when you try to delete the 3rd one.

For Each mysheet In xlbook.Worksheets
    If mysheet.Name = "Sheet1" Or mysheet.Name = "Sheet2" Or mysheet.Name = "Sheet3" Then
        mysheet.Name = "XXX_" & mysheet.Name
        'mysheet.Delete
    End If
Next mysheet

Open in new window

Wayne Taylor (webtubbs)

Martin, that was from my suggestion here.

Martin Liss

Martin, that was from my suggestion here.

I missed that, sorry.

Norie

Why not create the new workbook with only one sheet?

xlbook = Workbooks.Add(xlWBATWorksheet)

Open in new window

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bryce Bassett

ASKER

Thanks all for your suggestions. For the sake of moving forward, I went with Wayne's workaround of just hiding the sheets from the user, but of course it doesn't explain why the issue was happening in the first place.