Code to automatically generate new worksheet

Hello, guys. I am a newcomer to VBA. I would like to have some help from you guys. Please help!

I need a set of VBA code that generate worksheet according to a template and fill in a cell in each new worksheet according the cell values in a list.

In an excel workbook, I have a well developed-template to download stock data using Reuters add-on. In cell A1, it is the ticker of the stock.

I hope to have the following function. I hope to select some cells which contained the tickers. Then I run the macro. It generates the spreadsheets and rename them to the tickers. It fills in cell A1 with the tickers. That's it. The sequence of the worksheets should follow the sequence in the master list.

Thanks a lot!

I forgot to mention. The selection in the master list is vertical and continuous. For example, if I select A1 to A10. Then the macro copies some templates, named "template" for simplicity. It changes the names of the new spreadsheets to the cell values one by one. And also changes the cell A1 accordingly.

Code:

---

Sub SamT()
'For help see: http://www.vbaexpress.com/forum/show...-new-worksheet

Dim Cel As Range
Dim NewSheet As Worksheet

'Application.ScreenUpdating = False 'Uncomment after testing
For Each Cel In Selection
  Sheets("Template").Copy After:=ActiveSheet
  Set NewSheet = Sheets(Sheets.Count)
  With NewSheet
    .Name = Cel
    .Range("A1") = Cel
    .Activate
  End With
Next Cel
Cel.Parent.Activate
Application.ScreenUpdating = True
End Sub

---

Excel displayed:

Run-time error '9': Subscript out of range

How do I fix it? Also, sequence of the spreadsheets is important. Thank you :)

Check the worksheet name is correct

Thanks for helping. But the code did not work as expected.

Excel displayed Error #9 still. I named the template "Template". I guess I am right on that.

Suppose I put the code and the selected cells in "Worksheet 1".
A1=1
A2=2
A3=3
I select only these 3 cells.

After the code is run, there are two sheets called "Template (2)" and "1". Inside worksheet "1", cell A1 is 1. Nothing else.

The result I hope to have: After the code is run, 3 worksheets created and named 1,2 and 3 respectively. Inside the sheets, A1= 1 or 2 or 3, respectively.

Btw, the sequence of the worksheets is all-important. It should follow the 1-2-3 sequence. In this case, is the counter function better than the "for each cell" function?

Here is an example of the result that I hope to have.

Thanks a lot. I know I may be a bit demanding. :)

I found out that Sheets.Count always returns to right most tab. This means that its Index number is the same as its positional number By using the Index number of the sheet to copy after, the loop is just as simple.

Code:

---

Option Explicit

Sub SamT()
    'For help see: http://www.vbaexpress.com/forum/show...-new-worksheet
   
    Dim Cel As Range
    Dim i As Long
   
    i = Sheets(ActiveSheet.Name).Index
    'Application.ScreenUpdating = False 'Uncomment after testing
    For Each Cel In Selection
        Sheets("Template").Copy After:=Sheets(i)
        i = i + 1 'Index number of new sheet
        With Sheets(i)
            .Name = Cel.Text
            .Range("A1") = Cel.Value
            .Activate
        End With
    Next Cel
    Application.ScreenUpdating = True
End Sub

---