[RESOLVED] Selecting data for extraction

[thelocaluk]

I have put together this excel form for collecting data which is working well. What I now want is to have a way of selecting a starting and ending date for the dates of the data I want to extract from the data list, to an external workbook.I want to do this from another form, maybe by using a date picker method. I have attached the file, there are 2 passwords, "getmein" will open the workbook and "start" will open the form. I am a total newbie and this will be a great help as I have no idea where to start.
Thanks

[DKenny]

Try the following. The Test procedure is just to show you the main ExportData procedure in action.

Option Explicit
 
Sub Test()
    ExportData "12/01/2005", "12/31/2005"
End Sub
 
 
Sub ExportData(dStartDate As Date, dEndDate As Date)
Dim wksDataSheet As Worksheet
Dim wkbExport As Workbook
    
    'The worksheet to be exported
    Set wksDataSheet = Worksheets("IquaDB")
    
    'Turn off any existing filters
    wksDataSheet.AutoFilterMode = False
    
    'Filter the data based on the passed date parameters
    wksDataSheet.Range("A1").AutoFilter _
        field:=1, _
        Criteria1:=">=" & dStartDate, _
        Operator:=xlAnd, _
        Criteria2:="<=" & dEndDate
    
    'Add a new workbook
    Set wkbExport = Application.Workbooks.Add
    
    'Copy the data to the new book
    wksDataSheet.Range("A:J").Copy Destination:=wkbExport.Worksheets(1).Range("A1")
    
    'Clear the clipboard
    Application.CutCopyMode = False
    
    'Turn the filters off
    wksDataSheet.AutoFilterMode = False
       
    'Clear the object variables
    Set wksDataSheet = Nothing
    Set wkbExport = Nothing
End Sub

[thelocaluk]

Excellent Dkenny

What I have done is created a couple of date pickers to select the dates from a form but its not quite working.

Private Sub cmdOk2_Click()
Dim dStartDate As Date
Dim dEndDate As Date

dStartDate = dpStart()
dEndDate = dpEnd()
 
 ExportData dStartDate, dEndDate
End Sub

The date pickers have got there day and month format back to front. Can I tell it to use dd/mm/yyyy format instead??

[DKenny]

Change the datatype on dStartDate and dEndDate to Long.

[thelocaluk]

Change the datatype on dStartDate and dEndDate to Long.

Sorry to be a bit thick
How do I do that?

[DKenny]

Private Sub cmdOk2_Click()
Dim dStartDate As Long
Dim dEndDate As Long
 
dStartDate = dpStart()
dEndDate = dpEnd()
 
 ExportData dStartDate, dEndDate
End Sub

[thelocaluk]

Did that, but it now come ups with a ByRef argument type mismatch at the ExportData line.

[DKenny]

try changing
Sub ExportData(dStartDate As Date, dEndDate As Date)
to
Sub ExportData(ByVal dStartDate As Date, ByVal dEndDate As Date)

[thelocaluk]

Hey DKenny, I changed the
Sub ExportData(dStartDate As Date, dEndDate As Date)
to
Sub ExportData(dStartDate As Long, dEndDate As Long)
and that seems to be working just great.
Your an absolute guru, many thanks for all your help.

P.S I've read many post here as a keen newbie to VB and often come across your name. Keep up the good work, its a great help to people like me.

[DKenny]

Thanks, glad I could help.
Don't forget to mark your post as resloved