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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Chitosunday

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

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!)
 
Upvote 0

Forum statistics

Threads
1,186,916
Messages
5,960,562
Members
438,484
Latest member
supermon

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