Hi, I sent a reply to this email a while ago. But when I went to resend it today, I found some errors which are now fixed. I also removed some lines of code which weren't necessary.
I've modified your macro below. I've used For Each / Next to loop through each cell in the UsedRange, and Like rather than Find I can't test it properly without your workbook, so you'll have to do that and let me know how it goes. Sub Button3_Click() Dim ws As Worksheet, i As Long Dim c, t As Long i = 1 t = 1 For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then For Each c In ws.UsedRange If c Like "Directory *" Then i = i + 1 Worksheets("Report").Range("A" & i) = ws.Name c.Resize(, 2).Copy Destination:=Worksheets("Report").Range("B" & i) End If If c Like "*.ost" Then t = t + 1 c.Resize(, 2).Copy Destination:=Worksheets("Report").Range("C" & t) End If Next c End If Next ws End Sub (I've found when copying and pasting code straight from an email to a VBA module, it comes with all sorts of spurious spaces and carriage returns, which have to be removed, and sometimes even pastes as one super-long line. Sometimes I have to paste it into an XL spreadsheet first, then re-copy and paste it into the module.) Let me know if it works. Regards - Dave > Date: Thu, 15 Jul 2010 14:56:47 -0700 > Subject: $$Excel-Macros$$ Help me add a loop. > From: n_na...@yahoo.com > To: excel-macros@googlegroups.com > > Please help asap! I really appreciate it. > > The code below will search through other worksheets and copy and paste > back the search criteria to the next line of a "report" worksheet. It > works well except that it only does the first hit in each worksheet > and I need it to find multiple. I think alls it needs is another For > Loop under the Loop that goes to the next worksheet but I dont know > how to implement that. > > Thanks! > > > Dim Found As Range, ws As Worksheet, i As Long > Dim Foundtwo As Range, t As Long > i = 1 > t = 1 > > > For Each ws In ThisWorkbook.Worksheets > > If ws.Name <> ActiveSheet.Name Then > Set Found = ws.UsedRange.Find(what:=" Directory*") > If Not Found Is Nothing Then > i = i + 1 > Worksheets("Report").Range("A" & i) = ws.Name > Found.Resize(, 2).Copy > Destination:=Worksheets("Report").Range("B" & i) > End If > End If > > If ws.Name <> ActiveSheet.Name Then > Set Foundtwo = ws.UsedRange.Find(what:="*.ost") > If Not Found Is Nothing Then > t = t + 1 > Foundtwo.Resize(, 2).Copy > Destination:=Worksheets("Report").Range("C" & t) > End If > End If > > Next ws > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 7000 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe _________________________________________________________________ Need a new place to live? Find it on Domain.com.au http://clk.atdmt.com/NMN/go/157631292/direct/01/ -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe