Create worksheet based on lists & populate data from another... [Solved/Closed]

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

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: http://ccm.net/faq/27361-excel-a-macro-to-create-and-name-worksheets-based-on-a-list

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.

See more 

5 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 28, 2013 at 10:23 PM
7
Thank you
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 
                Err.Clear 
                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 
                Sheets(trainingSheet).Range(trainingRange).Copy 
                 
                'paste training material 
                Sheets(employeeName).Cells(1, "A").PasteSpecial 
                Application.CutCopyMode = False 
            End If 
        End If 
        nameStartRow = nameStartRow + 1 'increment row 
    Loop 
End Sub 

Thank you, rizvisa1 7

Something to say? Add comment

CCM has helped 1833 users this month

harvdog13 2 Posts Thursday February 28, 2013Registration date March 1, 2013 Last seen - Mar 1, 2013 at 12:27 PM
Thanks a bunch, worked perfectly. You're amazing.
bharathvg 1 Posts Thursday May 23, 2013Registration date May 23, 2013 Last seen - 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