Excel worksheet auto generator [Solved/Closed]

Report
Posts
3
Registration date
Monday March 1, 2010
Status
Member
Last seen
March 1, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
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 automaticly generate a new set of timecard each week. I need it to name the sheets with the employee's name on trhe 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 whitch will be in alphabetical order it will make the new card with the names and 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 need to be done.

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
One thing that is not clear is what happens to last week timesheet. Lets assume that this is just a template and every week you are making a copy to work off

This is how you can create your weekly time sheet
1. loop thru every name on sheet 1
2. for each name found, make a copy of master sheet and give it the same name as name found
3. put rest of the information
4. move to next name

Here is a thread in which some of what is suggested is being done
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Monday March 1, 2010
Status
Member
Last seen
March 1, 2010
1
i have nerver witten anything like that I have no idea what to do with it
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
You never worked with VBA macro ?
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

No that is hy I am starting with excel to see how they work and how to make them before I try Access and my work is rushing me to get it done so I just want to add some flash to it so it will help with my review.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > Chemopp
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 every thing out for you. </gras>

1. Press ALT + F11 to open vbe
2. Click on Insert, and select module
3. 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 NEED
'####################################################


    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
Hi I also tried this and it works really well except it only creates 1 sheet despite me having multiple names in column A of the names sheet?? any suggestions??
Posts
3
Registration date
Monday March 1, 2010
Status
Member
Last seen
March 1, 2010
1
I want to make it where when I update iton sunday I and print them out and it will delete the time sheets and then when I change the date it will automaticly generate the new time sheets for the next week. They will make each timesheet from the master sheet the master sheet will never change or be deleted. This way the new weeks timesheet will made automaticitly after the last weeks will be printed and be deleted. I am sending my Email if you write back I will send you what I have to show you ChemOpp@aol.com
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Well still as I said, the thread with minor modification will do the job for you. Take a look at it

https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions­on-for-excel-champions
Posts
1
Registration date
Wednesday October 30, 2013
Status
Member
Last seen
October 30, 2013

AS per Rimmer's comment above ... oops I submitted incorrectly!!))


Hi
I also tried this and it works really well except it only creates 1 new sheet despite me having multiple names in column A of the names sheet?? any suggestions??

I would be more than happy to make the named range or even a fixed length list eg 50


This is my first time with VB so please be gentle with me on the technical side;-))
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
You may have the wrong info, Check code comments. It is basing # of sheets on column 1