Excel VBA Autofilter date between two cells in each rows

try change

        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            .Offset(, 1).Rows(1).Resize(, 5).Copy Sheets("sheet1").[a1]
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With

to

        With .Range("b8", .Range("b" & .Rows.Count).End(xlUp)).Resize(, 9)
            Sheets("sheet1").Range("a1").Resize(, 5).Value = .Offset(, 1).Rows(1).Resize(, 5).Value
            .AdvancedFilter 2, r, Sheets("sheet1").[a1].CurrentRegion
        End With