VBA is searching under US Date range and not UK.

Hello,

You have to be coherent with your own regional date format ...

You could test following instruction

Call CreateSubsetWorkbook(Format(strStart, "dd/mm/yyyy"), Format(strEnd, "dd/mm/yyyy"))

Hope this will help

Ive run the StartDate and EndDate both ways and it returns the correct data. I am located in the United States.

????

Quote from Logit

Ive run the StartDate and EndDate both ways and it returns the correct data. I am located in the United States.

????

By default ... VBA uses the US format mm/dd/yyyy ...

Hello,

Tested at my end ... it is working fine ...

Attached is your test file

Hope this will help

It works here running both methods of entering the dates. That is why I don't understand .. something is amiss.

I can enter 08/04/20 or 04/08/20 and it still returns the correct data.

???

.

Here is a similar macro that can be edited to fit your needs. At present it does not create a new workbook and of course it does not

search Col A.

Sub Copy_Click()

    Dim startdate As Date, enddate As Date
    Dim rng As Range, destRow As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim c As Range
    

    Set shtSrc = Sheets("Main")
    Set shtDest = ActiveSheet

    destRow = 3 'start copying to this row
    
    On Error GoTo errorhandler
    
    startdate = CDate(InputBox("Begining Date - Ex: 7/1/2019", "Begining Date"))
    
    enddate = CDate(InputBox("End Date - Ex: 7/31/2019", "End Date"))
    
    'don't scan the entire column...
    Set rng = Application.Intersect(shtSrc.Range("J3:U500"), shtSrc.UsedRange)

    For Each c In rng.Cells
        If c.Value >= startdate And c.Value <= enddate Then
            
            c.Offset(0, 1).Resize(1, 5).Copy _
                          shtDest.Cells(destRow, 8)
            c.EntireRow.Copy shtDest.Cells(destRow, 1)

            destRow = destRow + 1

        End If
    Next
    
errorhandler:
Exit Sub

End Sub

Hello,

Just checked your file and found out you are using UK format but Locale has a regional setting Azerbaijani - Cyrillic ....

Is that right ?

so, in terms of Number Format on your computer you should test : NumberFormat = "dd/mm/yy;@"

Hope this will help

Hello again,

You are using an Input Box to populate your local dates as strings ... so you are adding a step of potential mistakes ...

You could test the following call instruction

Call CreateSubsetWorkbook(Format(DateValue(strStart), "dd mm yy"), Format(DateValue(strEnd), "dd mm yy"))

Hope this will help

Dates are stored in Excel as numbers so try converting to numeric values

Call CreateSubsetWorkbook(CLng(strStart), CLng(strEnd))

Regarding your initial Input Box you are using for allowing User Input, you could ensure the input is checked with following test :

Public Sub TestUserInputDate()
Dim strDate As String
  strDate = InputBox("Insert Your Date using Format dd/mm/yy", "User Input Date", Format(Now(), "dd/mm/yy"))
  If IsDate(strDate) Then
    strDate = Format(CDate(strDate), "dd/mm/yy;@")
    MsgBox strDate & " has a correct format "
  Else
    MsgBox "Wrong date format"
  End If
End Sub

Hope this will help

Having dealt with the many issues relating to dates when not using the American date format, this is a very common issue.

For auto-filtering, best to convert your input date strings into "proper" dates and then into longints as underneath the date format, Excel stores dates as a number.

Obviously, adding whatever validation routine/s you like to confirm the input dates entered are valid is a good idea - and yes, you would have to add some error checking as a result. However, assuming that dates are entered correctly as dd/mm/yy (or dd/mm/yyyy) strings then the below changes work fine on my version of Excel (I'm in Australia, so dd/mm/yyyy is the default format here).

Note that I've also tinkered with the filter range in the code snippet below because the code was not using the header row (row 2) in filtering. This meant the row 3 was pretty much copied every time irrespective of the start/end date entered.

            'should include the header row in the filter
            Set rngFull = .Range(.Cells(2, 1), .Cells(lngLastRow, lngLastCol))
            
            'Apply a filter to the full range to get only rows that
            'are in between the input dates
            With rngFull
                .AutoFilter Field:=lngDateCol, _
                            Criteria1:=">=" & CLng(CDate(StartDate)), _
                            Criteria2:="<=" & CLng(CDate(EndDate))
                
                'remove the header row before copying matching rows
                Set rngFull = rngFull.Offset(RowOffset:=1).Resize(Rowsize:=rngFull.Rows.Count - 1)
                'Copy only the visible cells and paste to the
                'new worksheet in our output workbook
                Set rngResult = rngFull.SpecialCells(xlCellTypeVisible)
                rngResult.Copy Destination:=rngTarget
            End With

One way to maybe simplify the hassle of validating the date input could to be use named ranges on the sheet containing the "Run" button. This would provide the opportunity to add Excel cell level validation and formatting which might overcome some of the hurdles when using an InputBox.

An example of how this could work is attached (again, my default date format is dd/mm/yyyy).

copy and archive with cell input.xlsm

I'm not quite sure what you mean by "I’ve noticed the dates must be sorted first for it to be effective otherwise it only copies data over up to a point."

Auto-filtering should not need data to be sorted first. I think you'll need to provide more information about what you're seeing or perhaps another example workbook as the sample one only has a very limited number of rows.

And, yes, you can of course cut & paste to the new workbook and remove the "empty" rows with more code.

That's because the code loops through the worksheets:

For Each wks In ThisWorkbook.Worksheets

If you only want it to work on the "pandp" sheet just change it accordingly.

Revised example attached.

copy and archive with cell input.xlsm