VBA copy records that are between two date / times

Question

I am trying to create a report in Word that drags data from an Excel spreadsheet. Being on a works PC I am limited as to what I can do (cant open word from excel macro's) so my work-around is to copy all the Information I need from one excel worksheet into another so that is properly formatted / arranged as a data source for a word mail merge.

The problem I have is that I want to copy the records that run between 07:00 on one day to 07:00 the next. It went a bit wrong when I added a nested IF for the times.

Any help is much appreciated, Rgds Iain

Sub CopyFromLog()
Dim LastRow As Long
Dim i As Long, j As Long, ns As Date, nf As Date, o As Date, f As String, s As String, t As Date

With Worksheets("Log")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With


With Worksheets("Data")
Worksheets("Data").Rows("3:" & LastRow).Clear
j = .Cells(.Rows.Count, "B").End(xlUp).Row + 2

End With

With Worksheets("Navigation")
ns = Worksheets("Navigation").Cells(3, "C").Value ' the report start date 
nf = Worksheets("Navigation").Cells(4, "C").Value ' the report end date
End With


For i = 2 To LastRow

With Worksheets("Log")
o = Worksheets("Log").Cells(i, "B").Value 'start date
t = Worksheets("Log").Cells(i, "V").Value 'end date
s = Worksheets("Log").Cells(i, "R").Value 'start time
f = Worksheets("Log").Cells(i, "W").Value 'finish time

If o <= ns And s >= "07:00" Then
    If t >= nf And f <= "07:00" Or t >= nf And f <= "R" Then

.Rows(i).Copy Destination:=Worksheets("Data").Range("A" & j)

j = j + 1

End If
End If

End With
Next i

End Sub` 

Answer 1

Here is a minor rewrite of your procedure. Refer to the comments for individual observations.

Sub CopyFromLog()
    Dim LastRow As Long, i As Long, j As Long
    Dim ns As Date, nf As Date, o As Date, t As Date
    Dim f As String, s As String

    With Worksheets("Log")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With

    With Worksheets("Data")
        ' You realize that LastRow was just defined as the last row on the Log workbook
        ' and you are now using it on the Data worksheet to clear rows
        .Rows("3:" & LastRow).Clear
        'I don;t understand this next line. You just cleared everthing from row 3 down so j should be 2
        j = .Cells(.Rows.Count, "B").End(xlUp).Row + 2
    End With

    With Worksheets("Navigation")
        ns = .Cells(3, "C").Value + TimeSerial(7, 0, 0) ' the report start dateTIME
        nf = ns + 1   '24 hours later
    End With


    With Worksheets("Log")
        For i = 2 To LastRow

            o = .Cells(i, "B").Value + CDate(.Cells(i, "R").Value)  'start dateTIME
            t = .Cells(i, "V").Value + CDate(.Cells(i, "W").Value)  'end dateTIME

            'I couldn't figure out your logic so I made my own.
            'Yours looked backwards and you were comparing f to the leffter "R" (f was never assigned a value either)
            If o >= ns And t < nf Then
                .Rows(i).Copy Destination:=Worksheets("Data").Range("A" & j)
                j = j + 1
            End If
        Next i
    End With

End Sub

The big difference is that I have discarded the notion of attempting to get a correct answer by comparing strings that look like times and resorted to bringing the times back into the dates so that the ns var would be something like 07/30/2016 07:00 I adopted a similar approach for all other datetimes in order that direct comparison could be made.