Delete worksheets in excel not using names

Microsoft Excel 2003 (full product)
December 2, 2010 at 05:31:44
Specs: Windows XP
I will try and give as much info on this as possible as I'm not sure it can be done.

I am using excel for my operators to save data. When they click on my macro button it creates a new sheet from a blank sheet named Fresh. The macro then gives the new sheet the name of the current date. I want only the last 30 worksheets saved and the rest deleted automatically. Here is where I see a problem with this I have 4 spreadsheets that I don't want to delete at the front of my workbook named Daily Chart, Pivot, Data Sheet, Fresh. After Fresh the dates start example would be 11-1-10. The date ones are the ones I only want 30 saved of. Any ideas on how to accomplish something like this would be very helpful and I would be very grateful.

Not sure if it will be helpful but here is my code for creating the worksheets. I can post all my code but its rather large as it saves data off the current date to my Data Sheet.

'Add Sheet as Last Sheet if it doesn't exist
  Sheets("Fresh").Copy After:=Sheets(Sheets.Count)
   With ActiveSheet
    .Name = Month(Date) & "-" & Day(Date)
'Set the current Date
    .Range("A2") = Date  
End With

See More: Delete worksheets in excel not using names

December 2, 2010 at 05:46:28
You want to keep Sheets 1 - 4 and then the last 30, right? That's 34 sheets, right?

So it sounds like you want Sheet 5 deleted after you add the 35th sheet.

Assuming you don't yet have 34 sheets, this will retain all sheets until you have more than 34, then it will delete Sheet 5 which should be your oldest date sheet.

If Sheets.Count > 34 Then Sheets(5).Delete

This will probably give you a warning message about "data in the sheet, etc.". You can subdue that message as follows:

Application.DisplayAlerts = False
  If Sheets.Count > 34 Then Sheets(5).Delete
Application.DisplayAlerts = True

As always, test this in a backup copy of your workbook since macros can nt be undone.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

December 3, 2010 at 04:24:53
Worked like a charm I knew if there was a way to do it people here would know. Thanks for the help.

Report •

December 3, 2010 at 05:36:25
The key is that if you use a sheet name, it looks like this:


If you want to refer to a sheet by its order in the sheet tabs, you refer to it by number:




You can also use variables for names and/or numbers:

mySht = "MySheetName"


mySht = 5



You can even build the sheet reference in a variety of ways:

myMonth = "May"
myDay = Sheets(1).Range("A1")
Sheets(myMonth & " " & myDay).

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question