Delete Active Worksheet

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I am trying to delete an activesheet based on the current date and have not been able to find any help in VBA help on this.

The background is that I have a number of sheets and when I reach the next month, I would like the oldest sheet deleted and a new sheet created. I will work on the sheet names and identity of the date and sheet myself, but my VBA code to delete the sheet will be something like :

Sub TimeBomb()
TimeValue1 = Now()
If TimeValue1 < DateValue("January 1, 2004") Then
Exit Sub
Else
ActiveWindow.SelectedSheets.Delete
End If
End Sub

My problem is that when I run this in January, the message appears: "Selected sheets will be permanently deleted" and I have to click on "Yes".

Is there a way to avoid this message, or have the macro choose "Yes" for me?
:rolleyes:
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hi tactps,
You can avoid the dialog box by using this:
Application.DisplayAlerts = False
right above your line "ActiveWindow.SelectedSheets.Delete".
(Just remember to use Application.DisplayAlerts = True
somewhere before the end of your routine.)

This help?
Dan

Edit:
Guess you got your answer...(Those weren't there when I started.
Man you guys are quick!)
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
That'll do it! The help on display alerts just confused me.

Thank you all!

:biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top