Code for Creating Worksheets in Excel

[nobull613]

Is there a way to use VB code in Excel (version 2002) to automatically create worksheets and rename them to a list of values in another worksheet.

I would have a list of employee numbers in one worksheet and worksheet that is a template, that would use the specific employee number to do a variety of vlookup functions.
I would like the code to loop through the list of numbers and for each number:
1- copy the template worksheet
2- move it to the end of the workbook
3- rename the worksheet to the employee's number
Repeat until the end of the list of numbers is reached.

Thank you for your assistance.

 

[AnotherHiggins]

First, a warning: Like data should be stored together.

That is, your life will be much easier in the long run if information for all employees is stored together on a single sheet. You can then use formulas on each of the employee-named sheets to pull information for that specific employee into that sheet.

This is opposed to the infinitely more cumbersome method of storing data for each employee on their own sheet and then trying to marry it all together on a summary sheet.

That having been said, yes, you can do what you asked.

Assuming you have a header row and your Employee IDs start in row 2 of Sheet 1; and assuming that your "Template" sheet is the second sheet in the workbook:

    '   Total Rows And Columns available in this version of Excel
    '**********************************************************
    dblAvailRows = ActiveSheet.Rows.Count
    dblAvailCols = ActiveSheet.Columns.Count

    '   Find last row
    '**********************************************************
    intLstRow = Sheets(1).Cells.Find( _
            What:="*", _
            After:=Cells(dblAvailRows, dblAvailCols), _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row

    '   Loop through employee IDs, copy first sheet and rename
    '   for each ID
    '**********************************************************
    For i = 2 To intLstRow
        strTmpName = Sheets(1).Cells(i, 1).Value
        Sheets(2).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = strTmpName
    Next i

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Mega ditto to John's advice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nobull613]

I'm not sure I explained it well the first time. John, you are right, the data is stored in one worksheet, with the template (and subsequent Employee worksheets) linked to that main data worksheet to display the individual employee's data.

I gave the code and try and it seems to work perfectly.
Thank you very much John.

 

[SkipVought]

Linked? If you mean by cell references, what a pain!

Have you tried using PivotTables to display individula employee data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}