Select Method of Range Class Failed error

[AZGJC]

I'm trying to expand on some code written by a previous person. I'm trying to have the macro identify up a defined date variable (uRepsonse)from 3 different spreadsheets. It works fine on the first sheet (this was the original code). I tried to copy the code for the 2nd and 3rd sheets, but on the 2nd sheet it stops at d.select with the error. Can anyone shed some light on this?

Thanks,
Garrett



Workbooks.Open "W:\!!Corr and Phone Stats\" & "Team Ruiz 2008.xls"
Workbooks.Open "W:\!!Corr and Phone Stats\" & "Team Parker 2008.xls"
Workbooks.Open "W:\!!Corr and Phone Stats\" & "Team Lopez 2008.xls"

'Chooses correct cell in file
Workbooks("Team Ruiz 2008.xls").Activate
Dim wksht As Worksheet, c As Range
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 + 1, ActiveCell.Column + 2).Select

'Chooses correct cell in file
Workbooks("Team Parker 2008.xls").Activate
Dim wksht1 As Worksheet, d As Range
For Each wksht1 In ActiveWorkbook.Worksheets
Set d = wksht1.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
If Not d Is Nothing Then
wksht.Activate
d.Select
Exit For
End If
Next
Cells(ActiveCell.Row + 1, ActiveCell.Column + 2).Select

'Chooses correct cell in file
Workbooks("Team Jovany 2008.xls").Activate
Dim wksht2 As Worksheet, e As Range
For Each wksht2 In ActiveWorkbook.Worksheets
Set e = wksht2.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
If Not e Is Nothing Then
wksht.Activate
e.Select
Exit For
End If
Next
Cells(ActiveCell.Row + 1, ActiveCell.Column + 2).Select

 

[SkipVought]

Hi,

Check your Worksheet object variable...

For Each wksht1 In ActiveWorkbook.Worksheets
  Set d = wksht1.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
  If Not d Is Nothing Then
    [b]wksht[red]1[/red][/b].Activate
    d.Select
    Exit For
  End If
Next

Check them ALL

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AZGJC]

Thanks. I was getting a duplicate error when I was renaming the wksht variable for each spreadsheet, so in my mind I needed to rename them. But after your reply it hit me that I only need to name the variables one time. Thanks again!