CODEWITHSUNDEEP
excelvba
Samuel Hulla
Samuel Hulla

Reputation: 7099

VBA Automation Error | How to remove sheets in a excel document copy?

I have the following Worksheet Layout

enter image description here

Now what I'm trying to do, is to create a copy of the document and keep only the two sheets Chase_list and Summary

(I however need to change the file extension from .xlsm to .xlsx so I can't use the SaveCopyAs method)

I attempted doing so with the following code (also the first line works properly, don't worry about it, variables are already defined, it's the actual worksheet deleting that does the harm)

        ThisWorkbook.SaveAs new_name & "_end_week_" & current_week - 1, 51, _ 
        accessMode:=xlExclusive
        Sheets("Chase_list").Cells.Copy
        Sheets("Chase_list").Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False

        Application.DisplayAlerts = False
        Sheets("Instructions").Delete
        Sheets("03_000002").Delete
        Sheets("Additions").Delete
        Sheets("Data").Delete
        Sheets("Control Panel").Delete
        Application.DisplayAlerts = True

Basically what it does, we first paste the data in chase_list as Values (because it's linked to another sheets we're removing) and once we do so, we start removing the sheets in the newly saved .xlsx file (maybe that fact it's not .xlsm anymore could do some harm?)

It seems to remove the worksheets sucessfuly like so:

enter image description here

Unfortunately, I am then presented with the following error

enter image description here

Any idea what am I doing wrong here?

Note: If I step through the code, it seems to crash on the last/pre-last line of code

Upvotes: 1

Views: 264

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57713

The issue here is that your sheet is named Control Panel (space) but you try to delete Control_Panel (underscore):

Compare: enter image description here

                                                            Sheets("Control_Panel").Delete
'                                                       space vs undersore ^

Improvement:
You can use a loop to delete all sheets besides Chase_list and Summary

Dim sh As Variant
For Each sh In Sheets
    If sh.Name <> "Chase_list" And sh.Name <> "Summary" Then
        sh.Delete
    End If
Next sh

Alternative:

If the error persists try to copy the worksheets into a new workbook and save that.

Sheets(Array("Chase_list", "Summary")).Copy
With ActiveWorkbook
    Sheets("Chase_list").Cells.Copy
    Sheets("Chase_list").Cells.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .SaveAs
End With

Upvotes: 1

Vityata
Vityata

Reputation: 43595

Probably you are trying to delete a sheet, which is the last one in Excel. And Excel returns 1004, when you try to do something like this:

Option Explicit

Public Sub TestMe()

    Dim wks As Worksheet
    Dim arrNames As Variant
    arrNames = Array("Chase_list", "Summary")

    For Each wks In Worksheets
        If Not valueInArray(wks.Name, arrNames) Then
            Application.DisplayAlerts = False
            If Sheets.Count > 1 Then wks.Delete
            Application.DisplayAlerts = True
        End If
    Next wks

End Sub

Public Function valueInArray(myValue As Variant, myArray As Variant) As Boolean

    Dim cnt As Long

    For cnt = LBound(myArray) To UBound(myArray)
        If CStr(myValue) = CStr(myArray(cnt)) Then
            valueInArray = True
            Exit Function
        End If
    Next cnt

End Function

You loop through the Worksheets and if you have more than 1 Sheets left, the wks is deleted. The valueInArray() is a boolean function, returning info whether the value was found in array.

Upvotes: 0

Related Questions