VBA export data to path dpends upon 1 combobox values sheets & 2 textbox date values

[johnodys]

Dear Sir

I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button

I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)

If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel" Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".

If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel" Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".

If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel" Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".


"C:\Users\mani\Desktop\New folder\Lenvo_Reports\ONSITE Cases\Vlokupuf" This is path i stored existing 3 file dispath,closed,cancel
Thanks in advance

find the attahcment

[alansidman]

This worked for me.

Option Explicit


Private Sub CommandButton1_Click()
    Dim wbk As Workbook
    Set wbk = ThisWorkbook
    Dim tgt As Workbook
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim w3 As Worksheet
    Set w1 = Sheets("DispatchCalls")
    Set w2 = Sheets("ClosedCalls")
    Set w3 = Sheets("CancelCalls")
    Dim i As Long
    Dim lr As Long
    Dim tlr As Long
    Dim sdte As Date
    Dim edte As Date
    sdte = Me.TextBox1
    edte = Me.TextBox2
    Dim aname As String
    Dim myPath As String
    myPath = "C:\Users\mani\Desktop\New folder\Lenvo_Reports\ONSITE Cases\Vlokupuf\"
    'myPath = "C:\Users\Alan Sidman\Desktop\"

Application.ScreenUpdating = False
If Me.ComboBox1 = "Dispatchcalls" Then
    aname = Me.ComboBox1
    Application.Workbooks.Add
    ActiveWorkbook.SaveAs (myPath & aname & ".xlsx")
    Set tgt = ActiveWorkbook
    wbk.Activate
    With w1
        .Range("A1:AQ1").Copy tgt.Sheets("Sheet1").Range("A1")
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lr
        tlr = tgt.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            If .Range("Y" & i) >= sdte And .Range("Y" & i) <= edte Then
            .Range("A" & i).EntireRow.Copy tgt.Sheets("Sheet1").Range("A" & tlr + 1)
            End If
        Next i
    End With
    Application.CutCopyMode = False
    tgt.Close

ElseIf Me.ComboBox1 = "Closedcalls" Then
    aname = Me.ComboBox1
    Application.Workbooks.Add
    ActiveWorkbook.SaveAs (myPath & aname & ".xlsx")
    Set tgt = ActiveWorkbook
    wbk.Activate
    With w2
        .Range("A1:BA1").Copy tgt.Sheets("Sheet1").Range("A1")
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lr
        tlr = tgt.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            If .Range("AS" & i) >= sdte And .Range("AS" & i) <= edte Then
            .Range("A" & i).EntireRow.Copy tgt.Sheets("Sheet1").Range("A" & tlr + 1)
            End If
        Next i
    End With
    tgt.Close
    Application.CutCopyMode = False
    
    
ElseIf Me.ComboBox1 = "Cancelcalls" Then
    aname = Me.ComboBox1
    Application.Workbooks.Add
    ActiveWorkbook.SaveAs (myPath & aname & ".xlsx")
    Set tgt = ActiveWorkbook
    wbk.Activate
    With w3
        .Range("A1:BA1").Copy tgt.Sheets("Sheet1").Range("A1")
        lr = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lr
        tlr = tgt.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
            If .Range("AK" & i) >= sdte And .Range("AK" & i) <= edte Then
            .Range("A" & i).EntireRow.Copy tgt.Sheets("Sheet1").Range("A" & tlr + 1)
            End If
        Next i
    End With

    tgt.Close
End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    MsgBox "Completed"


End Sub

Here is a tutorial for adding a Date Picker to your spreadsheet.

https://www.youtube.com/watch?v=yQw73kRgkGI

[Tinbendr]

Here's my effort.

I changed the textboxes to comboboxes, then only put unique dates in them.. This way, you can pick a start and stop date from the unique list.

[johnodys]

Thanks Sir Amazing Efforts !!!