Creating names from a list based on work schedule (I tried to use an if array but the blank spaces interfere)

[kayc1]

I am trying to create a list based on the information contained in a daily schedule Below is a sample of the data:


A B C D E F G H I
[TABLE="width: 752"]
<colgroup><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 3"]10/1/2015[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Notes[/TD]
[TD]Badge[/TD]
[TD]5/24/2015[/TD]
[TD]5/25/2015[/TD]
[TD]5/26/2015[/TD]
[TD]5/27/2015[/TD]
[TD]5/28/2015[/TD]
[TD]5/29/2015[/TD]
[/TR]
[TR]
[TD]Total Working[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Lt. [/TD]
[TD]0 xx[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sgt. 1[/TD]
[TD]0 xx[/TD]
[TD="align: right"][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sgt. 2[/TD]
[TD]0 xx[/TD]
[TD="align: right"][/TD]
[TD]1[/TD]
[TD]0 [/TD]
[TD]0 [/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]




I have alot more names below these 3. I am trying to get the list to show me their names if they are scheduled to work that day. (indicated by a 1) So far I have tried to use IF and Index in array. Each time I end up with the correct names but they spaces remain in the list when they are not scheduled to work. This causes an issue because the names are suppose to be referenced by another sheet and assigned their duties for the day.


My ultimate goal would be to have a macro button located under each date that would open an excel template and populate the information based on that dates data but that can come later first priority is getting the names to sort into a list with no spaces. (Also for some reason I cant get the autosort to remove the spaces either. One additional note the 0 are not 0 they are created by a formula.

 

[mumps]

I'm not sure if this is what you were looking for. This macro will create a worksheet for each date in the range D2:I2 and copy the names of people working on that date.

Sub AddSheet()
    Dim bottomA As Long
    bottomA = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim rng As Range
    Dim ColLetter As String
    Dim ws As Worksheet
    For Each c In Range("D2:I2")
        ColLetter = Replace(Cells(1, c.Column).Address(False, False), "1", "")
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        On Error GoTo 0
        If ws Is Nothing Then
            Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Replace(c.Value, "/", "-")
            For Each rng In Sheets("Sheet1").Range(ColLetter & "5:" & ColLetter & bottomA)
                If rng = "1" And rng <> "" Then
                    ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(rng.Row, 1)
                End If
            Next rng
        End If
    Next c
 End Sub