How do I create repeating worksheets in one workbook and have it .
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 |
All times are GMT +1. The time now is 07:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com