Delete worksheets after copying to new workbook macro

[excel_]

Hello all,

I have a macro that copies any tabs are not named "EnterNames" or "Details" and creates a new excel file with them.

I'm trying to delete those tabs from the original file after they are moved over.
I tried doing so with this line:

ThisWorkbook.Worksheets(strMyArray).Delete

It worked but excel crashes when I try to close or save afterwords. I don't get any error, it simply crashes.

The macro works great before I add that one line. I was inserting the delete line after this line:

ThisWorkbook.Worksheets(strMyArray).Copy

Is there a way to make it not crash? Also, when I had the delete line, excel asks: "Data may exist in the sheet(s) selected for deletion. To permanently delete the data press delete."
Is there a way to get rid of that warning message?


Here's the entire macro:

Sub MoveWorksheets()

    Dim strMyArray() As String
    Dim intArrayCount As Integer
    Dim wstMySheet As Worksheet
    
    intArrayCount = 0     
    Application.ScreenUpdating = False
    
    For Each wstMySheet In ThisWorkbook.Worksheets
        If wstMySheet.Name <> "EnterNames" And wstMySheet.Name <> "Details" Then
            intArrayCount = intArrayCount + 1
            ReDim Preserve strMyArray(1 To intArrayCount) 'Copy elements from the existing array to the new array
            strMyArray(intArrayCount) = wstMySheet.Name
        End If
    Next
        
    ThisWorkbook.Worksheets(strMyArray).Copy
        
    Erase strMyArray() 'Deletes the variable contents to free some memory
     
    Application.ScreenUpdating = True
    
End Sub

Thanks in advance!

 

[finchd4]

This little piece of heaven gets rid of the message.

Insert at beginning of point you dont want alerts to come up

Application.DisplayAlerts = False

Just remember to post its big brother at the end of your sub, or no messages will ever pop up again.

Insert at point that you will no longer have alerts.

Application.DisplayAlerts = True

I am no expert, sorry I can not help you with your deletion problem.

 

[excel_]

Thanks Finch!

Still playing around with the delete part. Hopefully someone has an idea.

Thanks again

 

[James Snyder]

I _think_ your problem is that you are trying to delete an array instead of the sheet named at each element of the array. Try something like this:

for i = 1 to array size
ThisWorkbook.Worksheets(strMyArray(i)).Delete
next i

Note that the array size is pseudocode, not an actual variable...

 

[excel_]

Thanks for the reply James. I'm not quite sure I follow though.
I am trying to delete that array but only because it's already defined with the tabs that I want to delete. I thought it would similiarly as to how it currently copies an array of tabs.

 

[excel_]

Still have not been able to get it working.
Anyone has any ideas and/or suggestions?