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


--
Carlos


"MuppetBaby" wrote:

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