Create worksheet based on lists & populate data from another...

harvdog13 Posts 2 Registration date Thursday February 28, 2013 Status Member Last seen March 1, 2013 - Feb 28, 2013 at 05:40 PM
 sv2bnv - Mar 10, 2014 at 02:27 PM

I've been recently working on trying to create a fairly large excel workbook & trying to automatically populate sheets inside of it.

I have a sheet called 'employee' & I want a new sheet to be created for each employee that is in that list. This part, I have working by using something similar to the post found here:

But, when I add a new name to the 'employee' sheet & try to re-run the macro, it bombs out saying "Cannot rename a sheet to the same name as another sheet...."

Any idea how I can edit the macro to ignore the sheets that have already been created & not toss that error?

Secondly, once the new employee sheets have been created, I'd like for it to auto populate with a list of training materials called 'training list'.

I feel like it could be done a similar way as the above, but since the sheets being created from the above macro aren't there until I run the macro, I cannot hard-code pages to copy the date to & would need to set a variable that I would use in the first macro like "sheet.employeenamecreated" and then have the training sheet populate that.

Sorry for the long post - any help on this? Thanks in advance.


1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 28, 2013 at 10:23 PM
Well I must compliment you on doing some thing that to me should be the most obvious thing to do. "Search". Amazes me that people don't even bother to conduct a simple search.

Now on to your issue. Try this
Option Explicit 

Sub CreateSheetsFromAList() 
    Dim nameSource      As String 'sheet name where to read names 
    Dim nameColumn      As String 'column where the names are located 
    Dim nameStartRow    As Long   'row from where name starts 
    Dim trainingSheet   As String 'training material sheet name 
    Dim trainingRange   As String 'range to copy from training material sheet 
    Dim nameEndRow      As Long   'row where name ends 
    Dim employeeName    As String 'employee name 
    Dim newSheet        As Worksheet 
    nameSource = "Employee" 
    nameColumn = "A" 
    nameStartRow = 2 
    trainingSheet = "Training" 
    trainingRange = "A2:B17" 'for example this is range we are going to copy 
    'find the last cell in use 
    nameEndRow = Sheets(nameSource).Cells(Rows.Count, nameColumn).End(xlUp).Row 
    'loop till last row 
    Do While (nameStartRow <= nameEndRow) 
        'get the name 
        employeeName = Sheets(nameSource).Cells(nameStartRow, nameColumn) 
        'remove any white space 
        employeeName = Trim(employeeName) 
        ' if name is not equal to "" 
        If (employeeName <> vbNullString) Then 
            On Error Resume Next 'do not throw error 
            Err.Clear 'clear any existing error 
            'if sheet name is not present this will cause error that we are going to leverage 
            Sheets(employeeName).Name = employeeName 
            If (Err.Number > 0) Then 
                'sheet was not there, so it create error, so we can create this sheet 
                On Error GoTo -1 'disable exception so to reuse in loop 
                'add new sheet 
                Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count)) 
                'rename sheet 
                newSheet.Name = employeeName 
                Application.CutCopyMode = False 'clear clipboard 
                'copy training material 
                'paste training material 
                Sheets(employeeName).Cells(1, "A").PasteSpecial 
                Application.CutCopyMode = False 
            End If 
        End If 
        nameStartRow = nameStartRow + 1 'increment row 
End Sub 
harvdog13 Posts 2 Registration date Thursday February 28, 2013 Status Member Last seen March 1, 2013
Mar 1, 2013 at 12:27 PM
Thanks a bunch, worked perfectly. You're amazing.
bharathvg Posts 1 Registration date Thursday May 23, 2013 Status Member Last seen May 23, 2013
May 23, 2013 at 02:45 AM
The VBA works funtastic. But m finding a problem with the row height and column width. The vba does not copy the same row and column width of the training sheet. How to make it possible??
i do have the same problem. row height and column width are not maintained. can anyone help me out with the solution ?
rizvisa1, I have to say that the VBA works smoothly.
It is very helpful.

Is it possible to auto link the cell from the employee sheet list to the newly created sheet?
For example:
We add to the list the name Tony and it create a new sheet named Tony.
Is it possible to auto link the name to the sheet?

Thank you in advance