A VBA code to create sheets based on a list

[seragrefaat]

I want to create and rename number of sheets based on values in range of cells, also after that i want to distribute each value of another list in a fixed cell, B39, of each sheet created.
I have already done the first part but i want someone to modify it to not fixed list (or range), i make the code with the range Q4:Q11, how to make the module executable when the rnge changes to Q4:Q7 or Q4:Q20? Thanks
/

Option Explicit
Sub CreateAndName6s()
    Dim c As Range
    Application.ScreenUpdating = False
    Sheets("Data").Select
    For Each c In Range("Q4:Q11")
        c.Select
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = c.Value
        Sheets("Template").Cells.Copy
        ActiveSheet.Paste
        Range("A1").Select
        Application.CutCopyMode = False
        Range("E4").Select
        ActiveSheet.Range("E4").Value = c.Value
        Sheets("Data").Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=c.Value & "!A1", TextToDisplay:=c.Value

    Next c
    Application.ScreenUpdating = True
End Sub

 

[seragrefaat]

I want to protect all the sheets created, and if i can add a password also it will be great.

 

[Fluff]

That is a totally different question, so will need a new thread. Thanks