Please see the enclosed VBA code whose purpose is to populate details sheets
from an input sheet. There is a subscript error 9 when run. The detail
sheets are called "Investments" , "Bank" , etc. If you need the excel file
please advise and I will send it. Any advise on how to shorten the code or
improve the error trapping also welcomed. The code used to work OK until I
added the Select Case logic which is aimed at determining the write to
location in the detail sheets named Investments, Bank etc.

Any ideas?

Stéphane

Option Explicit
Dim UtilityCodeRng As Range
Dim TBCodeRng As Range
Dim CancelA As Boolean

Sub ShuffleData()
CancelA = False
SetRanges
If CancelA = True Then Exit Sub
ShuffleAllData
MsgBox "Copying of data complete.", , "Done"
End Sub

Sub SetRanges()
With Sheets("TB")
Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
End With
If TBCodeRng(1).Address = "$A$1" Then
MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _
"This program will terminate.", , "No TB Sheet Data"
CancelA = True
Exit Sub
End If
End Sub

Sub ShuffleAllData()
Dim i As Range
Dim FirstRow As Long 'The first data row
Dim DestRow As Long 'The actual destination row
Dim FirstCol As Long 'The actual destination Column
Dim SecondCol As Long 'The actual destination Column number 2
Dim ThirdCol As Long 'The actual destination Column number 3

Sheets("TB").Activate
For Each i In TBCodeRng

'Note that i.Value is the destination sheet name.
Select Case i.Value
Case "Investments":
FirstRow = 2
FirstCol = 5
SecondCol = 7
ThirdCol = 10
Case "Bank Balances":
FirstRow = 2
FirstCol = 3
SecondCol = 4
ThirdCol = 7
Case "Share Capital & Reserves":
FirstRow = 2
FirstCol = 6
SecondCol = 10
ThirdCol = 12
End Select

'Let's say the destination range is defined as 30 rows max.
With Sheets(i.Value)
DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row
.Cells(DestRow, FirstCol) = i.Offset(, -5)

'Test for Dr or Cr amount current year, Looks at colum C
If i.Offset(, -4) = "" Then
.Cells(DestRow, SecondCol) = i.Offset(, -3)
'Writes at Destination content of column D
Else
.Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1)
'Writes content at destination of column C
End If
'Test for Dr or Cr amount for previous year (n-1), Looks at column F

If i.Offset(, -2) = "" Then
.Cells(DestRow, ThirdCol) = i.Offset(, -1)
'Writes content at destination of column F
Else
.Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1)
'Writes content at destination of column D

End If

End With
Next i
End Sub