Copying a row of Cells in a Range

[SimmonsDeux]

Guys,
I have this code that works wonderfully for what I needed at one point:

Option Explicit

Sub AutoAddSheet()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("INDEX").Range("A1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets(2).Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value
Next MyCell
End Sub

It copies everything in column A of my index sheet into a template sheet and repeats for every cell in column A.

My project has evolved and now I need it to copy a row of cells into specific cells on my template sheet. I need:

Col B from index to Cell B5 in Sheet2.
Col C from index to B9 in Sheet2.
Col D from Index to B8 in Sheet2.
Col E in Index to B7 in Sheet2.
Col F in Index to B3 in Sheet2.

Help will be greatly appreciated.

 

[BiocideJ]

This should do what you are asking.

Option Explicit
Sub AutoAddSheet()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("INDEX").Range("A1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
    Sheets(2).Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = MyCell.Value
    Sheets(Sheets.Count).Range("B5").Value = MyCell.Offset(0, 1).Value
    Sheets(Sheets.Count).Range("B9").Value = MyCell.Offset(0, 2).Value
    Sheets(Sheets.Count).Range("B8").Value = MyCell.Offset(0, 3).Value
    Sheets(Sheets.Count).Range("B7").Value = MyCell.Offset(0, 4).Value
    Sheets(Sheets.Count).Range("B3").Value = MyCell.Offset(0, 5).Value
Next MyCell
End Sub