Dave is correct, The wording No Sales in month ...... (thus I chose no sales) is part of the body of the sheet. I can't rely on the person to put the data in the same spot so I want to search each sheet for that type of wording somewhere on the sheet itself. Also the values for each sheet tab / name would differ so I thought it would be easier to just run through the group 1 by 1 without trying to account for specific names.
All of that said I am still getting an error of object required. On May 28, 6:16 pm, Dave Bonallack <davebonall...@hotmail.com> wrote: > Hi Paul, > > I think the op means that the text "NO SALES" can be in any cell somewhere on > the sheet - sheet name unknown, and not a sheet called "NO SALES". > > Hence his desire to look thru every cell on every sheet. (Cells.Select - > Selection.Find) > > And that's a good tip about counting backwards. I've come across that myself > when working down thru a bunch of rows, deleting some according to specific > criteria. I found a solution, but not as elegant as just working up from the > bottom. > > Regards - Dave. > > Date: Thu, 28 May 2009 04:43:38 -0700 > From: schreiner_p...@att.net > Subject: $$Excel-Macros$$ Re: Trouble with find > To: excel-macros@googlegroups.com > > 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 > > _________________________________________________________________ > Looking to move somewhere new this winter? Let ninemsn property > helphttp://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Eco... --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
