copy a template and fill in data from a single worksheet

[Jill]

I'm trying to make a macro to automate a tedious process of creating product
detail forms from a summary page.

Sheet 1 is the summary page containing data in columns A-N.The number of
rows is unlimited.

Sheet 2 is a template that would be filled in with the data from Sheet 1,
but each row in Sheet 1 would get its own individual worksheet - if I were
doing this manually, I would make as many copies of Sheet 2 as I have rows in
Sheet 1, and fill in certain cells on those sheets with the data in columns
A-N on Sheet 1. Also, the name of the worksheet would be the value in column
A.

For example, if Sheet 1 contains 10 rows of products, I would make 10 copies
of sheet 2 - the first copy would contain the data in row 1, the second copy
would contain the data in row 2, etc.

Is there a way to write a macro to automate this process?

 

[Patrick Molloy]

try this code:


Option Explicit
'replicate sheet 'Template'
'for each row in sheet 'Main'
Sub Main()
Dim RowIndex As Long 'row counter
Dim shNew As Worksheet
Dim source As Range
RowIndex = 1
Do While Worksheets("Main").Cells(RowIndex, 1) <> ""

Set shNew = Worksheets.Add(Worksheets(Worksheets.Count))
shNew.Name = "row_" & Format(RowIndex, "0000")

'copy some stuff
With Sheets("Main")
Set source = .Range(.Cells(RowIndex, "A"), .Cells(RowIndex, "N"))
End With
With source
shNew.Range("A2").Resize(.Columns.Count, 1).Value = _
WorksheetFunction.Transpose(.Value)
End With
'end of copy section
shNew.Range("a1") = "row #" & RowIndex

'next row
RowIndex = RowIndex + 1
Loop
End Sub