VB Script to populate template data from another sheet when copying

[trixie2396]

What I am trying to do is take my template (Sheet1) and run the macro so that it will create a new worksheet for every row in sheet2 and populate some of the data from sheet2. I have copied the VB script that accomplishes the new worksheet at the end. I am having a hard time with the next step, populating a few cells in the template (Sheet1) D6, E6, M3, and M4 with data from each row of sheet2 at the time it creates each new worksheet. Somehow I need to add1 to those fields to populate them but I can't figure out where to do this. In the VB script?

First row of data from Sheet2 to create new worksheet from Sheet1
A2 populate D6
B2 populate E6
C2 populate M3

Next row of data from Sheet2 to create another new worksheet from Sheet2
A3 populate D6
B3 populate E6
C3 populate M3

Here is what I have that creates the new sheet from the first sheet and this works great.

Private Sub AddWorksheets()
Dim c As Range
For Each c In Sheets("Sheet2").Range("A2:A4")
If c.Value <> "" Then
Worksheets("Sheet1").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = c.Value
End If
Next c
End Sub

 

[trixie2396]

Can anyone assist me... I am getting desperate... I have tried to many things in the script but nothing is working correctly.