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.

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

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

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

Middle names I'am going to have to add in to a new column. I noticed that
some of the names on the list have middel names in the First names. I am
going to have to a text to columns by delimited by space.

"Dave Peterson" wrote:

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

If you spend some time cleaning up your data before hand, it'll make life a lot
easier later on.

Good luck.

DM HD wrote:

Middle names I'am going to have to add in to a new column. I noticed that
some of the names on the list have middel names in the First names. I am
going to have to a text to columns by delimited by space.

"Dave Peterson" wrote:

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


--

Dave Peterson

Hi Dave it looks like it is working however I just have to ad one more thing.
I ame trying to add

Range ("d8") .Value = mycell.offset (0, 4) Value.

This is for the dates i was talking about. They are in calumn F on the list
and the dates are showing as follows 20030709. But the macro is not pulling
them.

What do i needed to change?

"Dave Peterson" wrote:

If you spend some time cleaning up your data before hand, it'll make life a lot
easier later on.

Good luck.

DM HD wrote:

Middle names I'am going to have to add in to a new column. I noticed that
some of the names on the list have middel names in the First names. I am
going to have to a text to columns by delimited by space.

"Dave Peterson" wrote:

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


--

Dave Peterson

Are the typos just in your post:
Range ("d8") .Value = mycell.offset (0, 4) Value.

Since F is 5 columns to the right of A, use .offset(0,5)
should look more like:
..Range("d8").Value = myCell.offset(0,5).Value

And is the value in column F a real date formatted as yyyymmdd, then you could
do this:

with .range("d8")
.value = mycell.offset(0,5).value
.numberformat = "yyyymmdd"
end with

If the value in column F is just numbers, don't use this.




DM HD wrote:

Hi Dave it looks like it is working however I just have to ad one more thing.
I ame trying to add

Range ("d8") .Value = mycell.offset (0, 4) Value.

This is for the dates i was talking about. They are in calumn F on the list
and the dates are showing as follows 20030709. But the macro is not pulling
them.

What do i needed to change?

"Dave Peterson" wrote:

If you spend some time cleaning up your data before hand, it'll make life a lot
easier later on.

Good luck.

DM HD wrote:

Middle names I'am going to have to add in to a new column. I noticed that
some of the names on the list have middel names in the First names. I am
going to have to a text to columns by delimited by space.

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

Everything worded. Thanks for your Help.

"Dave Peterson" wrote:

Are the typos just in your post:
Range ("d8") .Value = mycell.offset (0, 4) Value.

Since F is 5 columns to the right of A, use .offset(0,5)
should look more like:
..Range("d8").Value = myCell.offset(0,5).Value

And is the value in column F a real date formatted as yyyymmdd, then you could
do this:

with .range("d8")
.value = mycell.offset(0,5).value
.numberformat = "yyyymmdd"
end with

If the value in column F is just numbers, don't use this.




DM HD wrote:

Hi Dave it looks like it is working however I just have to ad one more thing.
I ame trying to add

Range ("d8") .Value = mycell.offset (0, 4) Value.

This is for the dates i was talking about. They are in calumn F on the list
and the dates are showing as follows 20030709. But the macro is not pulling
them.

What do i needed to change?

"Dave Peterson" wrote:

If you spend some time cleaning up your data before hand, it'll make life a lot
easier later on.

Good luck.

DM HD wrote:

Middle names I'am going to have to add in to a new column. I noticed that
some of the names on the list have middel names in the First names. I am
going to have to a text to columns by delimited by space.

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson