• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Expert

      VBA to select data based upon date and copy to another worksheet

      I have a spreadsheet with 11 columns – Column A to Column L. Column A has a date, I would like to have a button that a person presses the button a message will pop up asking to select a date or date range from Column A and then it would copy all data that is the rows relating to that date or date range in column A to column L and copy this into another worksheet.
      Transaction Date Call # Program Indentifier PSO Comments In order Obs Error BEA Comments – Description of Benefit Type – Error Monitoring date BEA
      September 29 2016 3 GCOS 999999 iiiiiii x October 21 2016 Kris Jones
      September 29 2016 4 GCOS 8888 llll x October 27 2016 Kris Jones
      September 29 2016 5 GCOS 77777 kkkkk x October 27 2016 Kris Jones
      September 29 2016 2 GCOS 1111999000 hhhhh x October 3 2016 Kris Jones
      September 29 2016 3 GCOS 999999 iiiiiii x October 21 2016 Kris Jones
      September 30 2016 4 GCOS 8888 llll x October 27 2016 Kris Jones
      September 30 2016 5 GCOS 77777 kkkkk x October 27 2016 Kris Jones
      A message puts up asking to select date so in this example let’s say September 29, 2016, then it would select all the data from each row from column A to Column L relating to September 29, 2016, copy it and input it in another worksheet. If the date input was September 29th and September 30th then everything that is related to these dates would be copied into another worksheet under the last blank row. Hoping that this is possible thank you again for all your help. Kris

      Asked by KOPower on October 11, 2016 in VBA.
      3 Answers
      Expert

      Hi Kris,

      As discussed please find the required solution here, I have mailed the working example to your email.

      Copy Data Based on Date Range

      UserForm1 Code:

      Private Sub cmdClose_Click()
      Unload Me
      End Sub
      Private Sub cmdSend_Click()
      Dim lngLastRowSource As Long
      Dim lngLastRowTarget As Long
      Dim strStartCol As String
      Dim strEndCol As String
      Dim dtDateStart As Date
      Dim dtDateEnd As Date
      Dim strSSht As String
      Dim strTSht As String
      'validate dates
      dtDateStart = TextBox1.Value
      dtDateEnd = TextBox2.Value
      If dtDateEnd < dtDateStart Then
      MsgBox "End Date Should Greater Than Start Date"
      Exit Sub
      End If
      strSSht = "SourceSheet"
      strTSht = "TargetSheet"
      'Change the Sheet names accrodingly
      'Specify the Columns to be Copied
      strStartCol = "A" 'Column A
      strEndCol = "L" ' Column L
      'source sheet | change this accordingly
      DataStartingRow = 9
      'last row with data
      lngLastRow = Sheets(strSSht).Cells(Rows.Count, "A").End(xlUp).Row
      'this is the last row in the target sheet
      lngLastRowTarget = Sheets(strTSht).Cells(Rows.Count, "A").End(xlUp).Row
      jCntr = 1
      For iCntr = DataStartingRow To lngLastRow
      If Sheets(strSSht).Cells(iCntr, 1) >= dtDateStart And Sheets(strSSht).Cells(iCntr, 1) <= dtDateEnd Then
      Sheets(strSSht).Range(strStartCol & iCntr & ":" & strEndCol & iCntr).Copy Destination:=Sheets(strTSht).Range("A" & lngLastRowTarget + jCntr)
      jCntr = jCntr + 1
      End If
      Next
      jCntr = jCntr - 1
      If jCntr = 0 Then
      MsgBox "No Records Copied"
      Else
      MsgBox jCntr & " Records Copied"
      End If
      End Sub
      Private Sub TextBox1_Enter()
      UserForm2.Show
      TextBox1.Value = pdtSlected
      TextBox2.Value = pdtSlected
      cmdSend.SetFocus
      End Sub
      Private Sub TextBox2_Enter()
      UserForm2.Show
      TextBox2.Value = pdtSlected
      cmdSend.SetFocus
      End Sub
      Private Sub UserForm_Initialize()
      cmdSend.SetFocus
      End Sub
      

       

      UserForm2 Code:

      Private Sub cmdSelectDate_Click()
      pdtSlected = MonthView1.Value
      Unload Me
      End Sub
      Private Sub CommandButton2_Click()
      Unload Me
      End Sub
      

      Code Module1:

      Public pdtSlected As Date
      Sub showmyForm()
      UserForm1.Show
      End Sub
      

      Thanks-Valli

      Answered by Valli on October 23, 2016..
      • Absolutely fantastic code, you guys are amazing !!!!!

        Thank you so much for all help and work on this !!! 🙂

         

        on October 23, 2016.
      Cancel
      Add comment
      Expert

      HI KOPower,

      Hope you are doing good.

      It’s possible. Please share sample workbook file with data. So that i will have better understand.

      Regards-Valli

      Answered by Valli on October 11, 2016..
      • How do I send you the sample workbook?

         

        on October 14, 2016.
      Cancel
      Add comment
      Expert

      HI KOPower,

      Please send your sample file to our email id  info@analysistabs.com. We will look into your data and get back to you.

      Regards – Valli

      Answered by Valli on October 15, 2016..