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


C

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
 
Ad

Advertisements

C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top