Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheet name in another workbook by variable (same sheet name)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheet name in another workbook by variable (same sheet name)
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing another workbook using a variable for the sheet? | Excel Worksheet Functions | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
selecting a single sheet from a volume of sheets in a workbook | Excel Worksheet Functions | |||
Print a sheet without ever selecting the sheet | Excel Programming | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) |