Excel - Worksheet auto generator

December 2016

Excel is a handy tool for number crunching and data representation. When combined with VBA it becomes a highly dynamic application that can be customized for innumerable scenarios. One such example is a case where one can create an auto generator of worksheets which would be generated every time an entry is added or deleted from a master list. The VBA code works in such a way that the auto generator looks for the names in master sheetand moves through all the sheets in the workbook. Wherever a pre-specified criteria is matched it can add or delete worksheets based on the user's input.


Issue


I have a workbook with a names sheet that has all the info I need put onto timecard and a master sheet with the timecard that the employee will fill out. How can I automatically generate a new set of timecard each week? I need it to name the sheets with the employee's name on the tab so they can find their card and put all the info from the name sheet onto their card. As new names are added or deleted to the list which will be in alphabetical order, it will make the new card with the names and will generate the right info on each card from the list sheet. How can I do this? I am new to this and this is the last thing that needs to be done.

Solution


I have no idea how your name and template looks like

Make a backup copy of your file

Read the code and make the appropriate changes, I have commented everything out for you.
  • Press ALT + F11 to open vbe
  • Click on Insert, and select module
  • Copy and paste the code below:


Sub generateTimeSheets()    

Dim sMasterNameSheet As String ' name of the sheet which has employee information    
Dim sTimeSheetTempate As String ' name of the sheet which is timecard template    

Dim iMaxNameCol As Integer 'the column number under which there  are most populated rows    
Dim lMaxNameRow As Integer    

Dim sTemp As String ' a temp variable    

Dim vWarning As Variant 'warning for deletion    


'#####################################################    
CUSTOMIZE HERE TO SUIT YOUR NEEDS 
'####################################################    


    sMasterNameSheet = "Names" 'this is the name of the sheet which has employee info    
    sTimeSheetTempate = "Timesheet Template" 'this is the name of the sheet which is timesheet template    
        
Dim iNameCol As Integer 'which col in employee information sheet, has name information (add similiary other columns)    
Dim sEmpName As String 'name of the employee    
            
    iMaxNameCol = 1 ' this column on employee sheet has maximum number of rows filled in    
    iNameCol = 1 'this is the column where employee name is found    
        
        
    vWarning = MsgBox("This will delete all sheets except " _    
                        & sMasterNameSheet & " and " & sTimeSheetTempate _    
                        & ". Press Yes to conitnue", vbCritical + vbDefaultButton2 + vbYesNo)    
        
    'do not wish to continue    
    If vWarning <> vbYes Then Exit Sub    
            
        
    ' to delete all but the two sheets    
    'move thru all sheets in book    
    For Each mysheet In Sheets    
        
        'sheet being examined in the loop    
        sTemp = mysheet.Name    
            
        'if sheet examine is not two critical sheet then delete it    
        If ((UCase(Trim(sTemp)) <> UCase(Trim(sMasterNameSheet))) And _    
            (UCase(Trim(sTemp)) <> UCase(Trim(sTimeSheetTempate)))) Then    
                
            mysheet.Delete    
        End If    
            
    Next    
        
    Sheets(sMasterNameSheet).Select    
        
    'find out the maximu number of rows    
    lMaxNameRow = Cells(65536, iMaxNameCol).End(xlUp).Row    
        
    sTemp = sTimeSheetTempate    
        
    For lThisRow = 2 To lMaxNameRow    
        sEmpName = Cells(lThisRow, iNameCol)    
        sEmpName = Trim(sEmpName)    
            
        If (sEmpName <> "") Then    
            
            Sheets(sTimeSheetTempate).Select    
            Sheets(sTimeSheetTempate).Copy After:=Sheets(sTemp)    
            ActiveSheet.Name = sEmpName    
            sTemp = sEmpName    
                
            'here you have to make the fixes    
            ' in this sample line it is saying that on the newly copied template, in its cell A1    
            'put the value found in Column A of employee sheet    
            Sheets(sEmpName).Range("A1") = Sheets(sMasterNameSheet).Range("A" & lThisRow)    
        End If    
nextFor:    
    Next    
        
End Sub
  • 4. Press F5 to run it

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Worksheet auto generator » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.