Excel VBA copy template worksheet

Question

I'm VBA noobs and in need to finish my assignment.

I would like to copy a template worksheets into and copy some cell automatically to it.
Here's what I get from webs, and I'm stuck now..

Sub CopyTemplate()
    Dim myCell As Range, MyRange As Range, Orange As Range
    Set MyRange = Sheets("Isolation Section").Range("B24")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    Set Orange = Sheets("Isolation Section").Range("D24")
    Set Orange = Range(MyRange, MyRange.End(xlDown))

    Application.ScreenUpdating = False
    For Each myCell In MyRange
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        With myCell
            ActiveSheet.Name = .Value
            ActiveSheet.Range("A13").Value = .Value
            ActiveSheet.Range("E13").Value = Orange.Value
            .Parent.Hyperlinks.Add Anchor:=myCell, Address:="", SubAddress:= _
               "'" & .Text & "'!B24", TextToDisplay:=.Text
        End With
    Next myCell
    Application.ScreenUpdating = True
End Sub

I have a template sheet called Template.
I create a copy of Template sheet and name it after each row from Isolation Section (so a loop).
Then place the cell data row B24 in cell A13 of the sheet.
But how about the cell data row D24 from Isolation Section copied to each sheets in new worksheets cell E13?

Image:

Sample Image

Sorry if my English is bad..

Your code doesn't add up. First, you need to remove copying the Template worksheet in your loop. Second, you need to clarify what changes you need to make in the newly copied Template worksheet. Clarify your question and specifically point out what's not working. As is now, it is unclear. — L42
In your edit, you never set the variable Orange. Also I think it is worth adding a screen shot or illustration of what you want to achieve. — L42
i cannot add the screen shot, my reputation not enough.. T.T — kireta7
Just post the link and I'll do it for you. Just upload it in a free image hosting website like this one: i.imgur.com — L42
imgur.com/WCmC7i1 , i just want Description row to automatically copied to green highlight — kireta7

L42 L42 · Accepted Answer · 2014-10-13T03:56:47

Try this:

For Each mycell In MyRange
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    With ActiveSheet
        .Name = mycell
        .Range("A1").Value = mycell.value
        .Range("E1").Value = mycell.Offset(0, 1).Value
    End With
    mycell.Parent.Hyperlinks.Add Anchor:=myCell, Address:="", SubAddress:= _
        "'" & mycell.Text & "'!B24", TextToDisplay:=mycell.Text
Next

Excel VBA copy template worksheet

2 Answers