none
Worksheet do not DELETE programatically using VBA RRS feed

  • Question

  • Hi

    In my macro, I am try to delete worksheets, but it does not delete.  Why?  What am I missing?  My code is:

    Workbooks("Mail Watch" & " " & YYYY & " " & Mon & " " & DD & ".xlsx").Activate
    Worksheets("Sheet1").Activate
    Sheet2.Delete
    Sheet3.Delete
        

    Friday, November 28, 2014 3:48 PM

Answers

  • Do you get an error message? If so, what does it say?

    Please note that Sheet2 in the line Sheet2.Delete is the code name of the sheet, which is not necessarily the same as the name displayed on the sheet tab in Excel.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by smsemail Friday, November 28, 2014 4:15 PM
    Friday, November 28, 2014 3:56 PM

All replies

  • Do you get an error message? If so, what does it say?

    Please note that Sheet2 in the line Sheet2.Delete is the code name of the sheet, which is not necessarily the same as the name displayed on the sheet tab in Excel.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by smsemail Friday, November 28, 2014 4:15 PM
    Friday, November 28, 2014 3:56 PM
  • Thank you Hans,

    It works now.  I did not have the deletes in the proper place in my logic.  As always, thank you so much for your help.  I value it! 

    smsemail

    my code is

      'new workbook name
        Mon = Format(Now(), "mm")
        DD = Format(Now(), "dd")
        YYYY = Format(Now(), "yyyy")
        NewMailWatchName = "Mail Watch" & " " & YYYY & " " & Mon & " " & DD
        
        'create new workbook
        Application.Workbooks.Add
        ActiveWorkbook.SaveAs (NewMailWatchName)
        Workbooks("MailWatchCleanUp.xlsm").Sheets("Watches").Copy before:=Workbooks("Mail Watch" & " " & YYYY & " " & Mon & " " & DD & ".xlsx").Sheets(1)
         
        Sheets("Sheet1").Select
        ActiveWindow.SelectedSheets.Delete
        
        Sheets("Sheet2").Select
        ActiveWindow.SelectedSheets.Delete
        
        Sheets("Sheet3").Select
        ActiveWindow.SelectedSheets.Delete
        
        Workbooks("MailWatchCleanUp.xlsm").Activate
        ActiveWorkbook.Close savechanges:=False
       
        Windows("Mail Watch" & " " & YYYY & " " & Mon & " " & DD & ".xlsx").Activate
           
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.Calculation = xlCalculationAutomatic

    Friday, November 28, 2014 4:15 PM