Deleting tab without using sheet name but object name

[Dkeller12]

I am looking for VBA code that will allow me to delete a sheet using its Object Name and not the sheet name. The reason I need this is I download a document on a monthly basis where the name of sheet1 is different each month. I know the first sheet is always sheet1 then the new file name in the project explorer. Can I this be done?

 

[Smitty]

Welcome to the Board!

Something like this:

Application.DisplayAlerts = False
    SH1CodeName.Delete
Application.DisplayAlerts = True

HTH,

 

[Dkeller12]

Smitty - Thanks for the quick response. I however am not getting that to work. I am getting an error on line 2 of the code (SH1CodeName.Delete). Should I be adjusting this code at all?

Thanks again.

 

[Smitty]

Yes, "SH1CodeName" needs to be replaced with the actual code name.

 

[Dkeller12]

Since I am trying to delete sheet1 my coding looks like this:

Application.DisplayAlerts = False
Sheet1.Delete
Application.DisplayAlerts = True

This however is giving me an error. I am getting it to work using the following code:

Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True

This way relies on the sheet I want to delete always being in the first spot in the spreadsheet. I would like to use the code you gave me but don't know why I am getting an error. Could the fact that I am running this code from a personal macro and not a worksheet macro be causing the error?

 

[shg]

Yes, it does matter; you can't use the codenames of sheets in one workbook from another workbook in that fashion. You could do it more convoluted:

    Application.DisplayAlerts = False
    With ActiveWorkbook
        .Worksheets(CStr(.VBProject.VBComponents("Sheet1").Properties("name"))).Delete
    End With
    Application.DisplayAlerts = True

 

[Dkeller12]

Ok, makes sense. Thank you so much for helping me out with this. Much appreciated.

 

[shg]

You're welcome, good luck.