selecting sheet name in another workbook by variable (same sheet name)

[Craig]

Hi everyone

I'm trying to move some data from one workbook that has assignments for
a division as a whole, to another workbook that has the assigments for
the individual members of that division. I've been running into a
couple of errors, right now, I'm getting a runtime error 1004,
application-defined or object defined error on the

myColumn = Cells(myRow, Columns.count).End(xlLeft).Column

line. Maybe I've been looking at this too long, but I can't seem to
find a way to fix this. Any ideas? Another problem I'm running into
is that if I get this working, I want the column that myColumn is
defined as to be no further left than column L, is there any way of
doing this other than creating a column, putting something (x's) in
then hiding it? Here's what I have so far. . .

Sub Assign()
Dim myAgent As String
Dim myType As String
Dim myJob As String
Dim myHours As String
Dim mySheet As String
Dim myMove As String
Dim myRow As Long
Dim myColumn As Long
mySheet = ActiveSheet.Name
If [e2].Value = "" Or [e3].Value = "" Or [e4].Value = "" Or
[e5].Value = "" Then
MsgBox ("Missing Info, need Agent, Project #, Hours, and Type")
Exit Sub
End If
myType = [e5].Value
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"
myAgent = [e2].Value
myJob = [e3].Value
myHours = [e4].Value
Windows("Support By Individual.xls").Activate
If ActiveSheet.Name <> mySheet Then Sheets(mySheet).Select
Selection.Find(What:=myAgent, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
myRow = ActiveCell.Row
ActiveCell.Offset(0, myMove).Select
ActiveCell.Value = myHours
myColumn = Cells(myRow, Columns.Count).End(xlLeft).Column
Cells(myRow, myColumn).Offset(0, 1).Activate
ActiveCell.Value = myJob
Windows("support by project.xls").Activate
End Sub

 

[Craig]

Sorry, I should have titled this post differently, I was actually
having a different problem when I started typing the post than I was
having when I actually posted. I guess that preview button is there
for a reason.