allison
04-03-2008, 11:48 AM
I hope that I can explain this well.
In the same workbook, I have:
Worksheet "Monthly Data" contains employee names.
Worksheet "Teams" contains team names (and employee names). There is a named range "TEAM" that has what I need.Dim FinalRow As Long
Dim SrcBook As Workbook
Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook
SrcBook.Activate
Sheets("Monthly Data").Select
With ActiveSheet
FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Team"
Range("H2").Select
Sheets("Teams").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"
Range("H2").Select
Sheets("Monthly Data").Select
Selection.AutoFill Destination:=Range("H2:H" & FinalRow)
Range("H2:H" & FinalRow).Select
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:C").Select
Range("C1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.NumberFormat = "hh:mm:ss"
Application.ScreenUpdating = True
End Sub
I am trying to do a vlookup by the employee name on Monthly Data to get the team name they are associated with.
When I run this code (I know that it's done with the recorder, but I'm still learning...), I get nothing in the field for the result.
My thought is that it's happening because I'm not selecting the Teams sheet at the correct point, but I've tried it in a few different places with still poor results.
Any suggestions to correct the error would be very helpful.
Thanks in advance.
In the same workbook, I have:
Worksheet "Monthly Data" contains employee names.
Worksheet "Teams" contains team names (and employee names). There is a named range "TEAM" that has what I need.Dim FinalRow As Long
Dim SrcBook As Workbook
Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook
SrcBook.Activate
Sheets("Monthly Data").Select
With ActiveSheet
FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Team"
Range("H2").Select
Sheets("Teams").Select
ActiveCell.FormulaR1C1 = _
"=+VLOOKUP(RC[-6],Teams!Teams,2,FALSE)"
Range("H2").Select
Sheets("Monthly Data").Select
Selection.AutoFill Destination:=Range("H2:H" & FinalRow)
Range("H2:H" & FinalRow).Select
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:C").Select
Range("C1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.NumberFormat = "hh:mm:ss"
Application.ScreenUpdating = True
End Sub
I am trying to do a vlookup by the employee name on Monthly Data to get the team name they are associated with.
When I run this code (I know that it's done with the recorder, but I'm still learning...), I get nothing in the field for the result.
My thought is that it's happening because I'm not selecting the Teams sheet at the correct point, but I've tried it in a few different places with still poor results.
Any suggestions to correct the error would be very helpful.
Thanks in advance.