selecting sheets of the same name between two workbooks

[Craig]

I'm trying to push some data from one workbook to another. Both
workbooks have sheets of the same name, so I was trying to use a
variable (mySheet), which is set in one workbook, then another workbook
is activated. Now I want to ensure that I'm on the same sheet as I was
in the first workbook (they are all week ending sheets, so the names
are dates). If the same sheet is already active in the second
workbook, the code works fine, if it's not, I get a subscript out of
range on the Sheets(mySheet).Activate line. Any ideas? Thanks in
advance for any help.

Sub Assign()
Call TurnOff
mySheet = ActiveSheet.Name
myJob = [data!e2].Value
myAgent = [data!e1].Value
myHours = [data!e3].Value
myType = [data!e4].Value
Dim mySheet As String
[c:c].Select
Selection.Find(what:=myJob, after:=ActiveCell, LookIn:= _
xlFormulas, lookat:=xlPart, searchorder:=xlByRows,
searchdirection:= _
xlNext, MatchCase:=False, searchformat:=False).Select
myRow2 = ActiveCell.Row
Range("V" & myRow2).Select
ActiveCell.End(xlToLeft).Activate
myColumn2 = ActiveCell.Column
Cells(myRow2, myColumn2).Offset(0, 1).Activate
ActiveCell.Value = myAgent
Cells(myRow2, myColumn2).Offset(0, 2).Activate
ActiveCell.Value = myHours
Windows("Support By Individual.xls").Activate
If ActiveSheet.Name <> mySheet Then Sheets(mySheet).Activate
[a2:a32].Select
Selection.Find(what:=myAgent, after:=ActiveCell, LookIn:= _
xlFormulas, lookat:=xlPart, searchorder:=xlByRows,
searchdirection:= _
xlNext, MatchCase:=False, searchformat:=False).Select
myRow = ActiveCell.Row
If myType = "EF" Then myMove = "6"
If myType = "Ops" Then myMove = "8"
If myType = "KB" Then myMove = "7"
If myType = "Conversions" Then myMove = "10"
If myType = "Processing" Then myMove = "9"
ActiveCell.Offset(0, myMove).Select
If ActiveCell.Value = "" Then
ActiveCell.Value = myHours
Else: CurHours = ActiveCell.Value
TotHours = (CurHours) * 1 + (myHours) * 1
ActiveCell.Value = TotHours
End If
Range("V" & myRow).Select
ActiveCell.End(xlToLeft).Activate
myColumn = ActiveCell.Column
Cells(myRow, myColumn).Offset(0, 1).Activate
ActiveCell.Value = myJob
Windows("support by project.xls").Activate
Call AutoSize
Call TurnOn
End Sub

 

[Craig]

Oh wait, I figured it out, I didn't have the sheet name I was using to
test this the same in both workbooks (one was 4-28-2006 and the other
was 04-28-2006). Any other thoughts on the code would be appreciated
though.