vba cells.find problem

Im writing a macro that will read data from one workbook, find the data in another workbook, copy the column associated with that data, and paste it into a new sheet. Im having trouble with the cells.find function... when i record a macro the code comes out like this:

Cells.Find(what:="Node Info: 443", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
Sheets("Sheet1").Select
Cells.FindNext(After:=ActiveCell).Activate

if i remove the sheet1.select line of code, it no longer works... but I dont know which sheet in the workbook the data will be on all the time so cannot include the sheet in my own code.

now my code:

Sub forloop()
c = 6

For i = 1 To 35
Windows("Monthly DP TC - Syracuse").Activate
Cells(i, 40).Select
If Not IsEmpty(Cells(i, 40).Value2) Then
mycell = ActiveCell.Value
mySearch = "Node Info: " & mycell
If Not UCase(Right(mycell, 5)) = "TOTAL" Then

Windows("VoIPReady - weekly - SYRNY - TW-CentralNY 2007-07-04.xls").Activate
Application.FindFormat.NumberFormat = "General"
'Sheets("SYRCNYAUB").Select

Set Z = Cells.Find(what:=mySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True)

If Not Z Is Nothing Then
Z.Activate

myrow = ActiveWindow.RangeSelection.Row
Rows(myrow).Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Activate
ActiveSheet.Range(Cells(c, 1), Cells(c, 24)).Select
ActiveSheet.Paste
c = c + 1

Else
MsgBox "Can't find " & mySearch
End If
End If
End If
Next i

End Sub


the problem is that the macro refuses to find the node number data using the cells.find command even though it exists on the searched workbook. It works when you provide it with the sheet that the data exists on, such as
Sheets("Sheet1").Select
but I do not know what sheets each node data exists on... thats why im doing a find.

thanks.