Select Data from Worksheet or worksheets based on userform entry

[Hutch1603]

Hello,

I have created a user form with 3 combo boxes and a DTPicker. The combo boxes allow me to select Name, Day and Price. Whichever day is selected determines which worksheet the data is stored on i.e. Tuesday goes to Tuesday Worksheet, etc.

My problem is with the second user form, which has a combo box and 2 text boxes. This form is to create a report of the data either by day or all days. The combo box is to select the day, while the text boxes are to allow me to pick a date range if required.

Selecting a single day is easy enough. Where I run into problems is when I select "All" for all days then try to specify dates. For example if I pick Wednesday with a start date, then I want all data after that date, providing there is no finish date entered. Like wise, if no start data is entered and an end date is, then I need all the data before the end date. Additionally, you could have start and end date set, then you only need the data between the two.

I am having problems getting the code to search for the date fields and then provide me with the data based on any off the 3 possibilities when using the dates. Can anyone suggest the correct way to check for these conditions?

 

[DanteAmor]

Hello,

I have created a user form with 3 combo boxes and a DTPicker. The combo boxes allow me to select Name, Day and Price. Whichever day is selected determines which worksheet the data is stored on i.e. Tuesday goes to Tuesday Worksheet, etc.

My problem is with the second user form, which has a combo box and 2 text boxes. This form is to create a report of the data either by day or all days. The combo box is to select the day, while the text boxes are to allow me to pick a date range if required.

Selecting a single day is easy enough. Where I run into problems is when I select "All" for all days then try to specify dates. For example if I pick Wednesday with a start date, then I want all data after that date, providing there is no finish date entered. Like wise, if no start data is entered and an end date is, then I need all the data before the end date. Additionally, you could have start and end date set, then you only need the data between the two.

I am having problems getting the code to search for the date fields and then provide me with the data based on any off the 3 possibilities when using the dates. Can anyone suggest the correct way to check for these conditions?

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Hutch1603]

Hello Dante,

Sorry for the delay in replying, been rather busy at work and this is the first opportunity I have to do what you asked. Link is as follows: https://www.dropbox.com/s/733dpu2rsbb9k12/Dance.xlsm?dl=0

Thanks

 

[DanteAmor]

Hello Dante,

Sorry for the delay in replying, been rather busy at work and this is the first opportunity I have to do what you asked. Link is as follows:

Thanks

The sheets are empty, in addition to the file you must send an explanation with examples, using the data from the file.
Without data I can not perform tests.

 

[Hutch1603]

Hi Dante,

Sorry for the delay. Here is a new link to the file, this time populated with test data. https://www.dropbox.com/s/733dpu2rsbb9k12/Dance.xlsm?dl=0

What I am trying to do is the following:

1) If the user selects all from userform1 combobox, then all the data from the named day worksheets will be copied to the report worksheet.
2) If the user only selects a single day, then all the data from that worksheet will be copied to the report worksheet.
3) The user can select either a single day or all days and a start date. Only data after the start date will be copied to the report sheet.
4) The user can select either a single day or all days and a finish date. Only the data before the finish date will be copied to the report sheet.
5) The user can select either a single day or all days and a start & Finish date. Only the data between the two dates will be copied to the report sheet.

 

[DanteAmor]

Hi Dante,

Sorry for the delay. Here is a new link to the file, this time populated with test data. https://www.dropbox.com/s/733dpu2rsbb9k12/Dance.xlsm?dl=0

What I am trying to do is the following:

1) If the user selects all from userform1 combobox, then all the data from the named day worksheets will be copied to the report worksheet.
2) If the user only selects a single day, then all the data from that worksheet will be copied to the report worksheet.
3) The user can select either a single day or all days and a start date. Only data after the start date will be copied to the report sheet.
4) The user can select either a single day or all days and a finish date. Only the data before the finish date will be copied to the report sheet.
5) The user can select either a single day or all days and a start & Finish date. Only the data between the two dates will be copied to the report sheet.

Try this

Private Sub CommandButton1_Click()
    Dim sh As Worksheet, shR As Worksheet, ini As Variant, fin As Variant
    Dim shs As Variant, d As Variant
    
    Set shR = Sheets("Report")
    shR.Rows("2:" & Rows.Count).ClearContents
    If ComboBox1 = "" Then
        shs = Array("Tuesday", "Wednesday", "Friday", "Saturday")
    Else
        shs = Array(ComboBox1.Value)
    End If
    For d = 0 To UBound(shs)
        Set sh = Sheets(shs(d))
        If TextBox4 = "" Then
            ini = Format(WorksheetFunction.Min(sh.Range("C2", sh.Range("A" & Rows.Count).End(xlUp))), "mm/dd/yyyy")
        Else
            ini = Format(CDate(TextBox4), "mm/dd/yyyy")
        End If
        If TextBox5 = "" Then
            fin = Format(WorksheetFunction.Max(sh.Range("C2", sh.Range("A" & Rows.Count).End(xlUp))), "mm/dd/yyyy")
        Else
            fin = Format(CDate(TextBox5), "mm/dd/yyyy")
        End If
        sh.Range("A1:E1").AutoFilter 3, ">=" & ini, xlAnd, "<=" & fin
        sh.AutoFilter.Range.Offset(1).Copy shR.Range("A" & Rows.Count).End(xlUp)(2)
        sh.ShowAllData
    Next
End Sub

 

[Hutch1603]

Thank you for your help Dante, I really appreciate it. Looks like everything is running the way I want now.

Regards

Hutch

 

[DanteAmor]

Thank you for your help Dante, I really appreciate it. Looks like everything is running the way I want now.

Regards

Hutch

I'm glad to help you. Thanks for the feedback.