Excel VBA: I switched to a new workbook and want to delete a sheet, but code is trying to delete sheet in macro workbook

1
votes

I am working on a rather large project for a cat rescue. I'm a volunteer and only know enough VBA to be dangerous. I have a spreadsheet that contains all my data. There are several sheets. I've written some code to get the data in order and then to go through the data and pull information for each cat and to create a new workbook that contains a sheet for each cat with the compiled information. I've got down to the last step, and don't know how to accomplish what I am trying to do. When the new workbook is created, it automatically adds a sheet called Sheet1. I add all the new sheets and would like to delete that initial Sheet1 which is blank. When I get to that point in the code by stepping through, I see that the debug shows that it is seeing the sheets in the first workbook that contains the data and the VBA code, and not the new workbook that has all the cat name sheets. I am not sure what I am missing. Here is a little section of the code that I am having issue with.

....
Workbooks.Open(path_and_newfilename).Activate

If (MyFunctions.Sheet_Exists("Sheet1") = True) Then  
    Application.DisplayAlerts = False
    Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True

End If
 




ActiveWorkbook.Save
1
excelvba

1 Answers

0
votes

Delete Worksheet

The function should have a workbook argument, so I think that this could easily fail.

With Workbooks.Open(path_and_newfilename)
    If MyFunctions.Sheet_Exists("Sheet1") Then
        Application.DisplayAlerts = False
        .Worksheets("Sheet1").Delete
        Application.DisplayAlerts = True
    End If
    .Save
    '.Close False
End With

Without the function:

With Workbooks.Open(path_and_newfilename)
    On Error Resume Next
    Dim ws As Worksheet: Set ws = .Worksheets("Sheet1")
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    .Save
    '.Close False
End With