VBA VLookup or equivalent

[Guest]

I've spent a couple of hours trawling the groups for an answer, so apologies
if it's there and I missed it.

I have an employee number in column b in a file named test, I want to lookup
the employee number in a file called nominal (column b) and return the
person's surname (column e) to column c in the file named test.

I'm trying to use VBA to achieve this as there are 31 sheets in the test
file, the data is entered each day and I don't want the users having to copy
and paste / drag formulas down.

I'm more than a little rusty as I'm returning to code after 5 years! Any
and all help greatly appreciated.

Jude

 

[Guest]

I think you should put all the data together in a single worksheet, instead
of having 31 worksheets. Suppose wks32 contains all the data, then you could
use the VLOOKUP function as usual. The following macro is a bit long, but I
think it will help you to get started.

Option Explicit
Public Sub subCopyWks()
Dim wbk As Workbook
Dim wks As Worksheet
Dim SourceRange As Range
Dim DestRange As Range
Dim i As Long

'Initialize wbk
Set wbk = ActiveWorkbook

'Initialize the destination worksheet
'Suppose the name of this worksheet that contains all
'data is wks32.
Set wks = wbk.Worksheets("wks32")

For i = 0 To 31
'Get the data from the source worksheet
With wbk.Worksheets(i + 1)
'Initialize the range that contains the data
'Suppose it goes from cell A1 to cell B10 in each worksheet
Set SourceRange = .Range(.Cells(1, 1), .Cells(10, 2))
End With
'Copy the data
SourceRange.Copy
'Initialize the destination range
With wks
Set DestRange = .Range(.Cells(i * 10 + 1, 1), .Cells((i + 1) * 10, 2))
End With
'Paste the data
'This command has several options that you should check
DestRange.PasteSpecial
Next i

'Clean
Set wbk = Nothing
Set wks = Nothing
Set SourceRange = Nothing
Set DestRange = Nothing
End Sub

 

[Guest]

Hi Carlos,

Thanks for your reply, but I'm not sure it resolves my issue. There are 31
worksheets because it's a daily overtime form, it calculates how many hours
each person has worked, what rate of pay they should be paid and whether they
are entitled to time off in lieu. The user specifically requested that there
is a tab for each day of the month. Also, the nominal file is a download
from Oracle HR. I wanted to use it in it's raw form rather than having to
copy and paste if possible.

Thanks

Jude