First, you're gonna have to look at your data once more.

You gave this:
Input:
A1:A313 last names
B1:b313 first names
c1:c313 client number
e1:e313 has date
Output:
d4:l4 merged, lastname
o4:v4 merged, firstname
z4:ac4 merged, middle name
am2:aq2 merged, client number

They fields don't match up. What happens to the Date? Where do the middle
names come from?

In any case, you should be able to modify this:

Option Explicit
Sub testme01()

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")

With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy after:=Worksheets(Worksheets.Count)
With ActiveSheet
On Error Resume Next
.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & .Name
Err.Clear
End If
On Error GoTo 0

'you're going to have to adjust these

.Range("d4").Value = myCell.Value
.Range("o4").Value = myCell.Offset(0, 1).Value
.Range("z4").Value = myCell.Offset(0, 2).Value
.Range("am2").Value = myCell.Offset(0, 3).Value
End With
Next myCell
End Sub

Just fix these lines:

..Range("o4").Value = myCell.Offset(0, 1).Value

O4 is on the new worksheet. It's getting the value one cell to the right of
column A on the list worksheet. (.offset(0,x) means x columns to the right).

==========
And the number of worksheets a workbook can have is limited by your pc's
memory. Personally, I think when you get too many (and too many depends on what
you're doing), it gets difficult to navigate to find things you want.




DM HD wrote:

Thanks for the reply on this.

From what i can see this might work. However I do not see how it will add
the names or information in to the cells. I might not be reading it right.

But this is what I am trying to do.

I have one worksheet with the information.

A:1 down to 313 has last names B has first names C has Client number and E
has date.

The Tempale document has the following that has to be duplicated. Will need
to have the above info placed in to.

Cells d,e,f,g,h,i,j,kl, row 4 are mearged to be one cell to have Last name
Cells o,p,q,r,s,t,u,v row 4 are for First name
cells z,aa,ab,ac row 4 are for Middle
Cells d,e,f,g,h,i,j,kl, row 8
And Client number am,an,ao,ap,aq row 2

I was told that here might be a limit how many worksheets that can be in a
document. Is it only up to 256? If so we ware willing to 2 documents.

"Dave Peterson" wrote:

If you have those names/numbers in a range of cells (like A1:A250 of a worksheet
named List), you could run a macro.

This may give you an idea:

Option Explicit
Sub testme01()

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")

With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy after:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

DM HD wrote:

I have an issue at the office where a co-worker is asking how to copy a
template excel worksheet 250 times to a workbook, but have first, last name
and file number filled in on each copy.

So have have 250 name's each having a file number. Each tab or sheet will
have a name and a file number.

--

Dave Peterson


--

Dave Peterson