------ Register to get rid of these "In Post" ads! ------


Hello,
I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
that can make a copy of sheet2 for each instance of employee number and name
it with the employee number as the sheet name?

thanks in advance for any help,
Robert

------ Register to get rid of these "In Post" ads! ------


Edit sheet names to suit where noted.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template") 'change to "Sheet2"
Set ListWks = Worksheets("list") 'Change to "Sheet1"
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 16:01:04 -0700, Robert <Robert@discussions.microsoft.com>
wrote:

>Hello,
>I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
>that can make a copy of sheet2 for each instance of employee number and name
>it with the employee number as the sheet name?
>
>thanks in advance for any help,
>Robert

------ Register to get rid of these "In Post" ads! ------


Why not just use data>filter>autofilter?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Robert" <Robert@discussions.microsoft.com> wrote in message
news:9BBDF8FA-5877-4EAD-9884-A045A7461F7B@microsoft.com...
> Hello,
> I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
> that can make a copy of sheet2 for each instance of employee number and
> name
> it with the employee number as the sheet name?
>
> thanks in advance for any help,
> Robert

------ Register to get rid of these "In Post" ads! ------


Hi,

Well I finally get a use for this feature:

1. Higlight the employee numbes in Column A and choose Data, Pivot Table and
Pivot Chart Report, click Next, Next and choose Existing worksheet and pick
an empty range starting on row 3 or lower, say D4, and click Finish.
2. Drop the title for the Employee # field in the Page Field area.
3. With the pivot table selected choose PivotTable, Show Pages and click OK.

4. Since you probably don't need the pivot tables, select the first employee
sheet tab, how down the shift key and click the last employee sheet tab.
5. Select the range with the pivot tables (empty ones) and press Delete.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

> Hello,
> I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
> that can make a copy of sheet2 for each instance of employee number and name
> it with the employee number as the sheet name?
>
> thanks in advance for any help,
> Robert

------ Register to get rid of these "In Post" ads! ------


Thanks Gord! It worked...there were a few errors "Please fix", but I just
kept cancelling and it took off and made all the sheets. Just for fun, I
tried to change the range to A6:A51 where the actual numbers are but I got VB
errors. Maybe the headers rows gave problems. I should have been more
specific. In any case, this saved me tons of time and I very much appreciate
it!
Robert

"Gord Dibben" wrote:

> Edit sheet names to suit where noted.
>
> Sub CreateNameSheets()
> ' by Dave Peterson
> ' List sheetnames required in col A in a sheet: List
> ' Sub will copy sheets based on the sheet named as: Template
> ' and name the sheets accordingly
>
> Dim TemplateWks As Worksheet
> Dim ListWks As Worksheet
> Dim ListRng As Range
> Dim mycell As Range
>
> Set TemplateWks = Worksheets("Template") 'change to "Sheet2"
> Set ListWks = Worksheets("list") 'Change to "Sheet1"
> 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
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 9 Jul 2009 16:01:04 -0700, Robert <Robert@discussions.microsoft.com>
> wrote:
>
> >Hello,
> >I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
> >that can make a copy of sheet2 for each instance of employee number and name
> >it with the employee number as the sheet name?
> >
> >thanks in advance for any help,
> >Robert
>
>