Trouble Looping through list to generate individual timecards

[alexthebaker]

Hello!

I'm trying to generate individual timecards for people based on data entered into a data range for a single week. I have a timecard worksheet that is filled with values from data on another sheet. I want to be able to create a copy of each persons' timecard and put them all into a new workbook.

I've made it so the timecard sheet is filled with one person's data based on their name appearing in a lookup cell and using VLOOKUP formula's to fill out the rest of the timecard. The data range also has a first column in which I'd like to be able to mark each line with an "x" to specify whether to include it when generating all the timecards.


I'm trying to figure out how to accomplish this but am very new to VBA and can't seem to figure it out from the pieces of code I've found. I think it needs to do something like this:


1. Select the first row in the data range marked with an "x" (sheet "Setup" range named "Crew").
2. Set the value in the VLOOKUP reference cell to the person's name in same row as step 1 (their name is in column 3 of the "Crew" range). The VLOOKUP reference cell is "D6" on worksheet "Home".
3. Create a new workbook with the week ending date (value in cell "K4" on worksheet "Setup").
4. Copy the now filled out timecard (sheet "Preview Card") to a new workbook as values.
5. Rename the sheet in the new workbook to the person's name (cell "D6" on worksheet "Home").
6. Select the next row and repeat for each line in the "Crew" range marked with an "x".


I would also like to protect each sheet in the new workbook so that only cell "DA1" is locked and the rest are editable. I'd also like to save the file relative to where the source file is (it'll be used by both Mac and PC users).


The Preview Card sheet is protected with a macro so that the other script can copy the worksheet but the user can't touch anything (I hope I did this right):

Private Sub Workbook_Open()
Sheets("Preview Card").Protect Password:="1234", UserInterFaceOnly:=True
Sheets("Preview Card").EnableSelection = xlUnlockedCells
End Sub

The little bit of code I have right now is:

Sub Generate()
    
    Sheets("Preview Card").Copy
    Cells.Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Preview Card").Name = person.Value
    Range("G5").Select


    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Generated TimeCards" & Application.PathSeparator & Format(Range("BR6"), "mm") & "-" & Format(Range("BR6"), "dd") & " - " & Range("F7") & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close


End Sub

I'm using Excel for Mac 2011 but would love for this to work with on a PC as well. Any help or direction would be greatly appreciate (and awesome!) This is my first time posting so I hope I'm putting this in the right place

Thanks,
Alex