you're off to a good start. However, the .Find function looks for things in the selected CELL. What you really want is to see if the sheet NAME is "No Sales".
Try:'========================================================== Sub test() for i = 1 to Sheets.Count if (ucase(Sheets(i).Name) = "NO SALES") then Sheets(i).Delete Exit For end If Next I End Sub '========================================================== Notice the "Exit For".. well, there can be only one sheet called "No Sales", so once it's gone, there no use in continue looking! Now, what if you expect sheets that CONTAIN the name "No Sales" (like "No Sales 1", "No Sales 2") then I'd use: '========================================================== Sub test() for i = Sheets.Count to 1 step -1 if (instr(1,ucase(Sheets(i).Name),"NO SALES") > 0) then Sheets(i).Delete end If Next I End Sub '========================================================== Notice that I used Sheets.Count to 1 instead of 1 to Sheets.Count. That is because: Let's say that there are 10 sheets. The third sheet is called "No Sales" If you loop from 1 to sheets.count, then when you get to I = 3, sheet 3 is deleted, sheet 4 then becomes sheet3, (sheet 5 becomes 4, etc) so the loop would then SKIP checking what was sheet 4. Then, when it got to i=10, it would ERROR, because there are not 10 sheets! by counting down, it eliminates this problem. hope this helps. Paul ________________________________ From: sjsean <sjsean95...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Wednesday, May 27, 2009 8:00:27 PM Subject: $$Excel-Macros$$ Trouble with find I have a set of worksheets where the data provider always puts a sheet with "No Sales". I am trying to write a macro that will remove this sheet (and others if they fit the above criteria). Sub test() For i = 1 To Sheets.Count Sheets(i).Select Cells.Select Dim r As Range Set r = Selection.Find(What:="No Sales", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate If Not r Is Nothing Then Sheets(i).Delete Next End Sub --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
