Adding rows for missing dates

Good morning

I'm trying to setup a attendance tracker, where I can copy/paste data from our time clock into a Excel worksheet. Have a button with VBA code that will look at a start date in cell D2 and the end date in cell G2, then compare it to data in column K, and add rows with the missing dates, excluding Sundays.

I have the below code it almost does what I'm looking for but it doesn't allow for my data ranges. I have attached a sample worksheet.

Can anyone please help with this issue.

Please Login or Register  to view this content.

Thanks
Dave

I have seen your sheet. logic seems to be confusing. I presume you want the dates to be serially filled for each sub sset of particualr flll name

for e.g. Jane Doe rows 5 to 9 there is no missisng data
for Henry Ford the dates between 22nd and 28th (total no. of 5 dates) are missig and you want to insert 5 rows. and fill up the same columns except for the date.

is this not what you want. This is a complicated task.

It would have been better if you have designed the data sheet like this

have one separate sheet for each name and write a common macro for each of the sheets.
re think on those line and write a macro.
even now you can copy the data from sheet 1 to each of the name sheet .

If this suggestion is ok revert back to newsgroup

AddRowsForMissingDates.xlsAddRowsForMissingDates.xls

PHP Code: 

Option Explicit
Sub AddRowsForMissingDates()
 Dim Cls As Range, Rng As Range, sRng As Range
 Dim Dat As Date, SoNgay As Integer, jJ As Long
     
 Sheet1.Select:                                     Dat = [D2].Value
 SoNgay = [g2].Value - Dat
 Sheets("GPE").Range("fName").Copy Destination:=[Ba1]
 Application.ScreenUpdating = False
 For Each Cls In [Ba1].CurrentRegion
    [ca2].Value = Cls.Value
    Range("B5").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("CA1:CA2"), CopyToRange:=Range("CA4:CK4"), Unique:=False
    Set Rng = [ck5].Resize(35)
    Rng.NumberFormat = "mm/dd/yyyy"
    For jJ = 0 To SoNgay
        Set sRng = Rng.Find(Format(Dat + jJ, "mm/dd/yyyy"), , xlValues, xlWhole)
        If sRng Is Nothing Then
            If Weekday(Dat + jJ) > 1 Then
                With [A65500].End(xlUp).Offset(1)
                    .Resize(, 3) = [Ca5].Resize(, 3).Value
                    Cells(.Row, "K").Value = Dat + jJ
                End With
            End If
        Else
        End If
    Next jJ
 Next Cls
 Application.ScreenUpdating = True
 [B5].CurrentRegion.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("K5") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom
End Sub