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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
 
Upvote 0
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,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top