Hi

For a VBA solution, that could be achieved more simply as

Sub proTest2()

Dim lastrow As Long, colno As Long
colno = Sheets("SL Paste TB").Range("F2").Value + 12
lastrow = Cells(Rows.Count, colno).End(xlUp).Row
Range(Cells(1, 5), Cells(65536, 5)) = ""
Range(Cells(1, 5), Cells(lastrow, 5)).Value = Range(Cells(1, colno),
Cells(lastrow, colno)).Value

End Sub

For a non VBA solution,
=INDEX(M:X,ROW(),'SL Paste TB'!$F$2)
and copy down

--
Regards
Roger Govier



"Newman Emanouel" wrote in message
...
Dear All

I managed to work it out and decided to post it for future reference by
all

Sub proTest()

If Sheets("SL Paste TB").Range("F2").Value = 12 Then
Columns("E:E").Value = Columns(" X:X").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 11 Then
Columns("E:E").Value = Columns(" W:W").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 10 Then
Columns("E:E").Value = Columns(" V:V").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 9 Then
Columns("E:E").Value = Columns(" U:U").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 8 Then
Columns("E:E").Value = Columns(" T:T").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 7 Then
Columns("E:E").Value = Columns(" S:S").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 6 Then
Columns("E:E").Value = Columns(" R:R").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 5 Then
Columns("E:E").Value = Columns(" Q:Q").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 4 Then
Columns("E:E").Value = Columns(" P:P").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 3 Then
Columns("E:E").Value = Columns(" O:O").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 2 Then
Columns("E:E").Value = Columns(" N:N").Value
Else
If Sheets("SL Paste TB").Range("F2").Value = 1 Then
Columns("E:E").Value = Columns(" M:M").Value
Else
MsgBox "No specific information is available about this state."
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

I would be grateful if there is an easier way to write the code that
someone
reply to this post, but the key is the legth of the rows in the column is
variable.


"Newman Emanouel" wrote:

Dear All

With the limitation of the if statement to on 7 variables I need help
with a
macro to do the following

I have twelve columns each representing a particular month of the year
with
financial data and account numbers along the left of the table. What I
want
to do is if the month is say 6 (Column "K") then pick up the value in the
column for the row pertaining to the account number for the 6th month and
return it to say column "A"

In the old days the If Statement use to work well but nolonger, can
anyone
please help with a vb script.

Thanks

Regards

Newman