Thanks JLG,
That seems like a good idea....but...

I made a new worksheet in my Book1 so that I no longer need to copy across
from Book2 directly, because my list wasn't displaying when Book2 was closed.
This new sheet is linked to Book2 and updates its data upon Book1 loading. So
now my list is referencing a dynamic range on a worksheet called "3" in
Book1....

.....anyway, there are 33 different items of data on each row, each in its
own column. This is both in Book2 and worksheet "3", and each column item has
a cell in Book1 sheet "Current Employees" that I want to copy to. I did out
the code by hand for the first two possible cases in for this list which
might give you a clearer idea of the complications, see bottom.

Basically, using your suggestion, after the THEN statement, there are
numerous cell referances which will change depending upon the list item from
1 to 3000 selected.Can you suggest the format of the code...something like:

Sub EmployeeData()

' My cell link for the list is on the calculations sheet cell A1.

Sheets("Calculations").Select
For Range("A1").Value

'The sheet called "3" is displaying the same data as Book2 in the same
layout. I want to copy my data from it.

Sheets("3").Select

'I will enter a list of numbers from 1 to 3000 in column AI of sheet "3" to
be equal to or different from the cell link value.

If (Range("AI2:AI3001).Value) = Range Then

'Now I get stuck because for one instance I'd simply select each cell I need
from sheet "3" to copy across to its corresponding cell on sheet 'Current
Employees', but I cant do that here, because depending upon the value of the
cell link I might want to copy the cells B2,C2,D2 etc to D9, D14,F17 etc, or
I might want to copy B1874,C1874,D1874 etc to D9, D14, F17 etc.....am I
explaining this ok?

Here's the way I did it using select case, but it took ages just to do these
two because after copy and pasting the code into the second case, I had to go
back over it by hand and change the values for ("B2") on the 4th line below
for Case = 1, to ("B3") for Case = 2, and I had to change this for C2 to C3,
D2 to D3....AH2 to AH3.
If using this method, I'll have to change every one of these values for
columns B to AH, for 3000 cases all the way down to
B3001,C3001,D3001,E3001,F3001 etc


Select Case (Range("A17").Value)
Case Is = 1
Sheets("3").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D9").Select
ActiveSheet.Paste
Sheets("3").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D11:F11").Select
ActiveSheet.Paste
Sheets("3").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D12:F12").Select
ActiveSheet.Paste
Sheets("3").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D14:F14").Select
ActiveSheet.Paste
Sheets("3").Select
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D15:F15").Select
ActiveSheet.Paste
Sheets("3").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D16:F16").Select
ActiveSheet.Paste
Sheets("3").Select
Range("H2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D17:F17").Select
ActiveSheet.Paste
Sheets("3").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D18:F18").Select
ActiveSheet.Paste
Sheets("3").Select
Range("J2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D20:F20").Select
ActiveSheet.Paste
Sheets("3").Select
Range("K2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D22:F22").Select
ActiveSheet.Paste
Sheets("3").Select
Range("L2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D24:F24").Select
ActiveSheet.Paste
Sheets("3").Select
Range("M2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D26:F26").Select
ActiveSheet.Paste
Sheets("3").Select
Range("N2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D28:F28").Select
ActiveSheet.Paste
Sheets("3").Select
Range("O2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D30:F30").Select
ActiveSheet.Paste
Sheets("3").Select
Range("P2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D32:F32").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Q2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D34:F34").Select
ActiveSheet.Paste
Sheets("3").Select
Range("R2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J6:L6").Select
ActiveSheet.Paste
Sheets("3").Select
Range("S2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J8:L8").Select
ActiveSheet.Paste
Sheets("3").Select
Range("T2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J10:L10").Select
ActiveSheet.Paste
Sheets("3").Select
Range("U2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J12:L12").Select
ActiveSheet.Paste
Sheets("3").Select
Range("V2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J14:L14").Select
ActiveSheet.Paste
Sheets("3").Select
Range("W2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J15:L15").Select
ActiveSheet.Paste
Sheets("3").Select
Range("X2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J16:L16").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Y2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J17:L17").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Z2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J18:L18").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AA2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J20:K20").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AB2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J22:K22").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AC2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J24:K24").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AD2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J26:K26").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AE2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J28:K28").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AF2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J30:K30").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AG2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J32:K32").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AH2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J34:K34").Select
ActiveSheet.Paste
Sheets("3").Select
Range("A2").Select
Sheets("Current Employees").Select
Range("A1").Select
Case Is = 2
Sheets("3").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D9").Select
ActiveSheet.Paste
Sheets("3").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D11:F11").Select
ActiveSheet.Paste
Sheets("3").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D12:F12").Select
ActiveSheet.Paste
Sheets("3").Select
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D14:F14").Select
ActiveSheet.Paste
Sheets("3").Select
Range("F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D15:F15").Select
ActiveSheet.Paste
Sheets("3").Select
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D16:F16").Select
ActiveSheet.Paste
Sheets("3").Select
Range("H3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D17:F17").Select
ActiveSheet.Paste
Sheets("3").Select
Range("I3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D18:F18").Select
ActiveSheet.Paste
Sheets("3").Select
Range("J3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D20:F20").Select
ActiveSheet.Paste
Sheets("3").Select
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D22:F22").Select
ActiveSheet.Paste
Sheets("3").Select
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D24:F24").Select
ActiveSheet.Paste
Sheets("3").Select
Range("M3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D26:F26").Select
ActiveSheet.Paste
Sheets("3").Select
Range("N3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D28:F28").Select
ActiveSheet.Paste
Sheets("3").Select
Range("O3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D30:F30").Select
ActiveSheet.Paste
Sheets("3").Select
Range("P3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D32:F32").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Q3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("D34:F34").Select
ActiveSheet.Paste
Sheets("3").Select
Range("R3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J6:L6").Select
ActiveSheet.Paste
Sheets("3").Select
Range("S3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J8:L8").Select
ActiveSheet.Paste
Sheets("3").Select
Range("T3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J10:L10").Select
ActiveSheet.Paste
Sheets("3").Select
Range("U3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J12:L12").Select
ActiveSheet.Paste
Sheets("3").Select
Range("V3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J14:L14").Select
ActiveSheet.Paste
Sheets("3").Select
Range("W3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J15:L15").Select
ActiveSheet.Paste
Sheets("3").Select
Range("X3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J16:L16").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Y3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J17:L17").Select
ActiveSheet.Paste
Sheets("3").Select
Range("Z3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J18:L18").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AA3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J20:K20").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AB3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J22:K22").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AC3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J24:K24").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AD3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J26:K26").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AE3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J28:K28").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AF3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J30:K30").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AG3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J32:K32").Select
ActiveSheet.Paste
Sheets("3").Select
Range("AH3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Current Employees").Select
Range("J34:K34").Select
ActiveSheet.Paste
Sheets("3").Select
Range("A2").Select
Sheets("Current Employees").Select
Range("A1").Select
End Select
Sheets("Current Employees").Select
End Sub