Solved Reports Sheet to copy rows between 2 dates from Data Sheet

September 25, 2013 at 20:59:04
Specs: 64 Bit, Windows 7 Enterprise, 8.00GB Intel (R) Core (TM) i5 CPU
The sheet named, 'Data' has a row for each item. Dates of each item are identified in column B.

I would be very greatful if someone could assist me in writing an excel 2010 macro that, when you press a button to exercise it, it has a dialogue box that asks for the start date and end date of the date range search you would like to run.

Once the dates are entered, it then needs to troll through the sheet named 'Data', copy any rows where the date falls within the date range and pastes them into the sheet named 'Reports'.

Please test in Excel 2010.

Thankyou in advance :)


See More: Reports Sheet to copy rows between 2 dates from Data Sheet

Report •


✔ Best Answer
September 26, 2013 at 22:10:05
Private Message.

Click on my user name, then click on it again on the page that opens.

You should end up in the Private Message section with a very basic text entry field.

Just be aware that I won't be responding until the moring, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 26, 2013 at 01:41:17
Are the dates sorted or are they scattered throughout the column in no specific order?

Can the Reports sheet be overwritten each time the macro is run or does it need to be retained?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
September 26, 2013 at 16:37:15
.The dates in column B are scattered through the 'Data' sheet in no particular order.
.The 'Reports' sheet can be overwritten every time it is run.

Thanks again for looking into it for me :)


Report •

#3
September 26, 2013 at 21:24:55
If you'll send me an email address via PM (don't post your email in this thread) I'll send you a workbook with some code that might get you started.

The reason I need to send you a workbook is because the code is written to work with the workbook I put together. It'll be a lot easier to send you the actual workbook than to try and explain how to set up the same workbook.

If the code is close to what you want, we can probably adapt it to your existing workbook.

Let me start by saying that there's an issue with this type of request:

When you press a button to exercise it, it has a dialogue box that asks for the start date and end date of the date range search you would like to run.

The issue is called "users".

Users are not very good at following instructions and odds are that they will enter an invalid date or enter a Start Date that is later than the End Date or something like that.

How I addressed that issue is to have the code build a Data Validation Drop Down list with the unique dates from the Data Sheet. With a Drop Down list, the user is forced to chose valid dates, in a format that the code will recognize.

Once the Start Date is chosen, another Drop Down is created for the End Date, but that Drop Down starts at the date chosen as the Start Date. That way the End Date will always be equal to or later than the Start Date.

If the user goes back and changes the Start Date after choosing the End Date, and the new Start Date is after the End Date, then the End Date field is cleared and the User has to chose a later End Date.

Once a valid pair of dates is entered, the user is prompted to run the extract. If he chooses Yes, the Report sheet will be filled in. If he chooses No, he can go back and change the dates.

It's not bullet proof, but it's a start. If it's close to what you want, I can offer suggestions to make even more idiot...errr, I mean...user proof.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 26, 2013 at 21:45:16
Thanks, sounds good. What's PM?

Report •

#5
September 26, 2013 at 22:10:05
✔ Best Answer
Private Message.

Click on my user name, then click on it again on the page that opens.

You should end up in the Private Message section with a very basic text entry field.

Just be aware that I won't be responding until the moring, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 1, 2013 at 21:51:34
Hi Derby Dad, thanks for the spreadsheet, having a look at it now.

message edited by Timnewtonoz


Report •

#7
October 10, 2013 at 20:37:01
Macro works, thanks heaps Derby Dad - you are a great Excel Wizard! I am posting it below for others to share :)

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lastDateRw, lastListRw As Long

'Determine if Date Picker Cell was selected
If Target.Cells.Count = 1 Then
If Target.Address = "$B$1" Then
Application.EnableEvents = False
'Clean Up Date Picker Page

With Sheets("Date Picker")
.Range("A:A").ClearContents
.Range("B2:C3").ClearContents
.Range("B2:B3").Validation.Delete
End With

'Create Filtered List of Dates from Data Sheet and Sort Column A

lastDateRw = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Data").Range("B1:B" & lastDateRw).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Date Picker").Range("A1"), Unique:=True

With Sheets("Date Picker").Sort
.SetRange Range("A:A")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Add Data Validation to B2
lastListRw = Sheets("Date Picker").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Date Picker").Range("B2").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A2:$A" & lastListRw
.IgnoreBlank = True
.InCellDropdown = True
End With

' Ask User to Choose a Date in B2, Format as Date
Sheets("Date Picker").Range("D2") = "<--- Choose a Start Date from Drop Down"
With Sheets("Date Picker").Range("B2")
.NumberFormat = "m/d/yyyy"
.Select
End With
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim RunAsk, d
Dim lastListRw As Long

'Determine if Date was chosen in B2 Drop Down
If Target.Cells.Count = 1 Then
If Target.Address = "$B$2" Then
Application.EnableEvents = False

'If user changes Date Picker Date to a Date that is after End Date, then clear B3
If Sheets("Date Picker").Range("B2") > Sheets("Date Picker").Range("B3") Then
With Sheets("Date Picker")
.Range("B3:C3").ClearContents
.Range("B3:B3").Validation.Delete
End With
End If

'Search Column A for Date Picker Date chosen
With Sheets("Date Picker").Range("$A:$A")
Set d = .Find(Sheets("Date Picker").Range("B2"))
End With

'Set Drop Down in B3 Date Pickering with Date from B2
lastListRw = Sheets("Date Picker").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Date Picker").Range("B3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A" & d.Row & ":$A" & lastListRw
End With

'Ask User for End Date, Format as date
Sheets("Date Picker").Range("D3") = "<--- Choose a End Date from Drop Down"
With Sheets("Date Picker").Range("B3")
.NumberFormat = "m/d/yyyy"
.Select
End With
Application.EnableEvents = True
End If

'Determine if User wants to run report

If IsDate(Sheets("Date Picker").Range("B2")) And _
IsDate(Sheets("Date Picker").Range("B3")) Then
RunAsk = MsgBox("Extract Data To Report Sheet?", vbYesNo, "Run Report")
If RunAsk = vbYes Then
RunNow
End If
End If

Application.EnableEvents = True
End If
End Sub
Sub RunNow()

Dim StartDate, EndDate As Date
Dim lastDateRw, srcRw, dstRw As Long


'Clear Report Sheet, Copy Row 1 from Data
Sheets("Report").Cells.ClearContents
Sheets("Data").Rows(1).EntireRow.Copy _
Destination:=Sheets("Report").Range("A1")
'Set Date Variables
StartDate = Sheets("Date Picker").Range("B2")
EndDate = Sheets("Date Picker").Range("B3")


'Loop through Data sheet, copying matching Rows to Report sheet
lastDateRw = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
For srcRw = 2 To lastDateRw
If Sheets("Data").Range("B" & srcRw) >= StartDate And _
Sheets("Data").Range("B" & srcRw) <= EndDate Then
dstRw = Sheets("Report").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("B" & srcRw).EntireRow.Copy _
Destination:=Sheets("Report").Range("A" & dstRw)
End If
Next

'Clear Drop Down to ensure fresh list each time
With Sheets("Date Picker")
.Range("A:A,B2:D3").ClearContents
.Range("B2:B3").Validation.Delete
.Range("A1").Activate
End With

'Show Report
Sheets("Report").Select

End Sub


Report •


Ask Question