Finding a specfic cell in multiple tabs

[AZGJC]

Hi,

Some time ago, someone helped me with the following code. What I am trying to do is find a cell based on a date given (uResponse), and then importing data from another sheet adjacent to that cell. This code works just fine. However, now I want to do this to more than 1 sheet, and I'm having trouble with that. So I want it to find the uResponse on sheet 1, and then bring in the data, then go to sheet 2 and find the uResponse, and then bring in the data, etc. etc. until there are no more sheets in the file (25 sheets). Any help would be apprecaited. Here's the code (the variables are defined earlier in the routine):


Workbooks("Large Group Indiv Scorecard.xls").Activate
For Each wksht In ActiveWorkbook.Worksheets
Set c = wksht.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
If Not c Is Nothing Then
wksht.Activate
c.Select
Exit For
End If
Next
Cells(ActiveCell.Row + 0, ActiveCell.Column + 1).Select

Thanks,
Garrett

 

[SkipVought]

Hi,

Try this...

    Dim wsSummary As Worksheet
    Workbooks("Large Group Indiv Scorecard.xls").Activate
    Set wsSummary = ActiveSheet
    
    With wsSummary
        For Each wksht In ActiveWorkbook.Worksheets
            If wksht.Name <> .Name Then
                Set c = wksht.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
                If Not c Is Nothing Then
                    wksht.Range(c, c.Offset(0, 1)).Copy _
                        Destination:=.Cells(wsSummary.[A1].CurrentRegion.Rows.Count + 1, "A")
        
                End If
            End If
        Next
    End With

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}