Excel 2007 : Macro needed to transfer data between tabs

Incase you run the macro again, the data in the Capital List tab will be appended to the end of each sheet. If you need to clear the data in all the sheets (except Capital List) before re-running the macro, then use this code instead of the code in post 2 -

Option Explicit

Sub copy_data()
Dim lrow As Long, i As Long
Dim sname As String

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> "Capital List" Then
        lrow = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
        If lrow = 3 Then GoTo nextsheet
        Worksheets(i).Range("A4:G" & lrow).ClearContents
    End If
nextsheet:
Next i

With Worksheets("Capital List")
    lrow = .Range("E" & .Rows.Count).End(xlUp).Row
    For i = 6 To lrow
        sname = "Year" & .Range("E" & i).Value
        .Range("A" & i & ":G" & i).Copy Worksheets(sname).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End With

Application.ScreenUpdating = True

End Sub