How to copy data between two defined dates to new worksheet using VBA?

Question

I am trying to copy data from a "Data" worksheet into a already created worksheet called "DateData". I want the user to be able to enter in a Start Date ("L15") and End Date ("L16") in a separate worksheet called "No Entry". On a button click...Then the data in "Data" worksheet is pulled into the "DateData" Worksheet, only including records between those dates (including the start and entry date). I hope that makes sense haha

I have tried the below but keep getting errors. The first being a "Sort method of Range class failed 1004". The code below also doesn't use the preset worksheet to copy data but creates a sheet at the end of all worksheets (which I don't want).

The "Data" worksheet has titles all in row 1 and data starts from A2 onwards...It has 19 columns of titles (so data filled) and the date that I want it looking for is in column G..G1=Title, G2 = Date starts. Date format = dd/mm/yyyy

How would I go about doing this? Any help would be so grateful. Thank you

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False

Dim StartDate, EndDate As Date
Dim MainWorksheet As Worksheet

StartDate = Sheets("NoEntry").Range("L15").Value
EndDate = Sheets("NoEntry").Range("L16").Value

Set MainWorksheet = Worksheets("Data")

MainWorksheet.Activate

Range("G1").CurrentRegion.Sort key1:=Range("G1"), order1:=xlAscending, Header:=xlYes

Range("G1").CurrentRegion.AutoFilter Field:=7, Criteria1:=">=" & StartDate, Operator:=xlAnd,        
Criteria2:="<=" & EndDate

ActiveSheet.AutoFilter.Range.Copy

Worksheets.Add after:=Worksheets(Worksheets.Count)

ActiveSheet.Paste

Selection.Columns.AutoFit

Range("G1").Select

MainWorksheet.Activate

Selection.AutoFilter

Sheets("NoEntry").Activate

End Sub

"DateData"

"Data"

So as you can see from the "Data" worksheet I have sorted the data but because it has blanks they are at the bottom (as in there are no dates in the G column for it). This was before validation so this happened

And what copies over onto the "DateData" worksheet is only the records with blank dates.

Sorry for the black filled records as they are private information. I hope that makes sense.

Answer 1

First, see How to avoid using Select in Excel VBA to learn how to avoid using select in your code. There is almost no necessary case in using it.

See below notes for the code I provide (now tested!).

1) You are having an issue where a worksheet is being added and you are not aware how/ why and you are uncertain of your destination for your data. To overcome this, it is a common practice to explicitly define your worksheet objects. This makes it easier for you to understand, while also allowing for less scope for error. I have qualified the worksheets as wsData for “Data worksheet”, wsDate for “DateData worksheet” and wsNoEntry for “No Entry worksheet”. Do you see how easy it is to understand now?

2) Make sure that the dates in your data set are stored as “Date” type values. You can do this under the number formatting ribbon.

3) I have chosen to use an array to loop through. Depending on how big your data set is, this will be a much faster way to loop through to get the start and end date

4) This approach assumes your data is sorted by the Date column (G)

Sub CopyDataUsingDateRange()

    Application.ScreenUpdating = False

    Dim wsData As Worksheet, wsDate As Worksheet, wsNoEntry As Worksheet
    Dim dSDate As Date, dEDate As Date
    Dim lRowStart As Long, lRowEnd As Long
    Dim aData() As Variant
    Dim i As Long


    'set the worksheet objects
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsDate = ThisWorkbook.Sheets("DateData")
    Set wsNoEntry = ThisWorkbook.Sheets("No Entry")

    'required variables
    dSDate = wsNoEntry.Range("L15").Value
    dEDate = wsNoEntry.Range("L16").Value

    'set the array - you can make this dynamic!
    aData = wsData.Range("A1:Z1000").Value

    'for loop to find start
    For i = 1 To 1000
        If aData(i, 7) = dSDate Then
            lRowStart = i
            Debug.Print "Start row = " & lRowStart
            Exit For
        End If
    Next i

    'now loop backwards to find end date
    For i = 1000 To 1 Step -1
        If aData(i, 7) = dEDate Then
            lRowEnd = i
            Debug.Print "End row = " & lRowEnd
            Exit For
        End If
    Next i

    'now we have start and end dates
    'going to use copy/ paste for simplicity
    wsData.Range("A" & lRowStart, "Z" & lRowEnd).Copy
    'paste in date sheet
    wsDate.Range("A1").PasteSpecial Paste:=xlPasteValues
    'clear clipboard
    Application.CutCopyMode = False

    Application.ScreenUpdating = True

End Sub

Hope this helps, mostly with understanding so you can leverage for future use!

Answer 2

Consider avoiding the use of constant .Select and .Activate. Instead, manage processes with Set variables or in a With context. Additionally, the filter copy method needs to be handled differently namely on visible and non-blank cell results of filtered worksheet.

Dim StartDate As Date, EndDate As Date
Dim MainWorksheet As Worksheet, NewWorkSheet As Worksheet

StartDate = Sheets("NoEntry").Range("L15").Value
EndDate = Sheets("NoEntry").Range("L16").Value

Set MainWorksheet = Worksheets("Data")

With MainWorksheet
    ' SORT RANGE
    .Range("G1").CurrentRegion.Sort key1:=.Range("F1"), order1:=xlAscending, Header:=xlYes

    Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    With .Range("$A:$G")
        ' SORT RANGE 
        .AutoFilter Field:=7, Criteria1:=">=" & StartDate, Operator:=xlAnd, _
                              Criteria2:="<=" & EndDate
        ' COPY VISIBLE AND NON-BLANK CELLS TO NEW WORKSHEET     
         Application.Intersect(.SpecialCells(xlCellTypeVisible), _
                               .SpecialCells(xlCellTypeConstants)).Copy _
                               Destination:=NewWorkSheet.Range("A1")
    End With        
    ' REMOVE FILTER
    .Cells.AutoFilter
End With

Sheets("NoEntry").Activate
Set MainWorksheet = Nothing: Set NewWorkSheet = Nothing