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

Answered

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

Expert Asked on October 11, 2016 in VBA,   VBA: Macros.
Add Comment
3 Answer(s)
Best answer

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

Expert Answered 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.
Add Comment

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

Expert Answered on October 11, 2016.

How do I send you the sample workbook?

 

on October 14, 2016.
Add Comment

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

Expert Answered on October 15, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.