Ways to extract data between two date ranges with a VBA button

[VonHelson]

So, I little backstory.

My work asked for people who were good at Excel to come forward as they had a project that needed doing. I came forward knowing that I am above average on excel. I've done some database work and some general VBA codes to extract data onto a different sheet but they're now asking for something that I've got no idea how to do.

So I've got Raw Data that covers columns up to M with over 4000 separate rows worth of information. All of the rows are financial services account information generated by around 70+ financial advisers and work have requested a program where you can input a date range, choose 1 of the advisers names, choose an account status (All, Completed, Application Submitted etc) and at a click of a button it extracts all application data for that adviser between the two date ranges. They want this on a separate sheet.

I've done some fiddling and some Googling but all I've got so far is this -

Sub CopyandFilter()

Worksheets("Raw Data Sheet").Activate

Range("A1").AutoFilter Field:=13, Criteria1:=

Selection.CurrentRegion.Select

Selection.Copy

Worksheets("KFI Data Only").Activate

ActiveSheet.Paste

Columns.AutoFit

Rows("1:1").Font.Bold = True

Worksheets("Raw Data Sheet").Activate

Sheet1.ShowAllData

Range("E23").Select

End Sub

But it isn't working at all as I've got no idea on how to get the VBA to look up the to and from date range.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or 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. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[VonHelson]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or 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. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi Mumps,

The data within the sheet contains sensitive material covered by data protection laws. I did consider removing the data and putting in dummy stuff but realised that as it's around 4380 cells over 13 columns across 4 sheets which would take ages to do.

 

[mumps]

I wouldn't need all the rows, 8 or 10 rows would be enough.

 

[VonHelson]

I wouldn't need all the rows, 8 or 10 rows would be enough.

So here's what the raw data looks like.

My brief is to be able to input a date range into specific cells on a different sheet (I've called it priority cases) choose one of the adviser names from a dropdown and then press a button to extract the data for that one adviser within the specific date range.

I've had a chat with work today and they've also asked if there is a way we can band together the initialloanamountrequested field into 10,000s so we can extract that data as well i.e £10,000.00 - £20,000.00 would extract all loans that fit within the specific input range for date and adviser but only pull out loans equal to or higher than £10K up to loans equal to or less than £20,000.00.

They aren't making this easy.

Thanks you for any help you can give me. It's really appreciated.

Broker Report 2020 Breakdown.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Account Number	Customers Names	TotalApplicants	StatusNameId	Broker Name	MaxLoanAmount	InitialLoanAmountRequested	CustomerRequestedFacility	LoanToValue	VariantCode	ProductCategory	ProductName	ApplicationDate
2	4486486	Davis	1	CancelledDeclined	Terence Harford	845,000.00	140,000.00	21,000.00	N/A	1	Cat 1	Lending 1	16/09/2019
3	4684646	Church	2	KFIComplete	David McDaniels	451,222.00	400,000.00	Lump Sum	N/A	2	Cat 5	Lending 1	14/10/2019
4	46454	McAfee	2	Completed	Foine McAnn	18,466.00	250,500.00	Lump Sum	N/A	3	Cat 7	Lending 1	20/05/2019
5	48949	Saheed	2	KFIComplete	Jason Eldridge	15,487.00	40,000.00	60,750.00	N/A	4	Cat 1	Lending 1	08/09/2020
6	3843854	Sheldon	1	Completed	Hery Wainscott	45,118.00	20,000.00	25,050.00	N/A	5	Cat 3	Lending 1	30/09/2019
7	1896186	Kurts	2	KFIComplete	Harriet Edwards	154,848.00	100,000.00	200,000.00	N/A	6	Cat 2	Lending 1	09/07/2020
8	3845348	Mills	2	KFIProgress	Richard Smith	78,954.00	100,000.00	Lump Sum	N/A	7	Cat 2	Lending 1	15/03/2020
9	78643	Nelson	2	CancelledDeclined	Jerry Porter	3,518,161.00	75,000.00	Lump Sum	N/A	8	Cat 6	Lending 1	15/05/2019
10	1864684	Trevors	2	CancelledDeclined	Oscar Nunez	7,815,316.00	200,000.00	110,000.00	N/A	9	Cat 7	Lending 1	03/07/2019
11	638685	Rochards	2	Completed	Matt Goddard	4,568,788.00	25,000.00	35,000.00	N/A	10	Cat 1	Lending 1	07/02/2020
12	1268438	Smith	2	Completed	Liam Gist	15,154.00	85,000.00	205,000.00	N/A	11	Cat 1	Lending 1	21/05/2020
13	386816	Nigels	1	Completed	Natasha Gunt	254,816.00	12,400.00	60,100.00	N/A	12	Cat3	Lending 1	18/02/2020
Sheet1

 

[mumps]

By "Advisor" I assume you mean "Broker". Is that correct? Can a broker be listed more than once in column E? You want a separate sheet named "Priority Cases" that will have a drop down list of all brokers, a cell to enter a start date, a cell to enter an end date, a drop down to select the low amount of the loan and another drop down to select the high amount of the loan. Then the data based on the above-mentioned cells would be extracted to the "Priority Cases" sheet. Is this correct?

 

[VonHelson]

By "Advisor" I assume you mean "Broker". Is that correct? Can a broker be listed more than once in column E? You want a separate sheet named "Priority Cases" that will have a drop down list of all brokers, a cell to enter a start date, a cell to enter an end date, a drop down to select the low amount of the loan and another drop down to select the high amount of the loan. Then the data based on the above-mentioned cells would be extracted to the "Priority Cases" sheet. Is this correct?

Hi Mumps,

Yes, sorry. It's the cell called Broker Names and yes, the name can be listed more than once in column E.

Your breakdown is correct. I've created the Priority Cases sheet already. Here's what I've got so far -

Broker Report 2020 Breakdown.xlsb
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
4
5
6		Priority Data
7
8
9
10
11
12		KFI Count		1193			To Date					From Date
13
14
15		Min Loan Amount					Max Loan Amount					Advisor
16
17
18
19
20
21
22
23
24
Priority KFI cases

Cell Formulas
Range	Formula
E12	E12	=COUNTIF('Raw Data Sheet'!D:D,"*KFIComplete*")

Named Ranges
Name	Refers To	Cells
'Raw Data Sheet'!_FilterDatabase	='Raw Data Sheet'!$D$1:$D$4512	E12:F12

 

[mumps]

Click here to download your file. I have re-created the Priority KFI Cases sheet to remove merged cells. You should avoid using merged cells because they almost always create havoc for Excel macros. Make you selections in the Priority KFI Cases sheet and click the "Extract Data" button. Please note that if you receive a message that no data was found, then one or more of the criteria was not found and you will have to change one or more values.

 

[VonHelson]

Click here to download your file. I have re-created the Priority KFI Cases sheet to remove merged cells. You should avoid using merged cells because they almost always create havoc for Excel macros. Make you selections in the Priority KFI Cases sheet and click the "Extract Data" button. Please note that if you receive a message that no data was found, then one or more of the criteria was not found and you will have to change one or more values.

Hi Mumps,

I really appreciate all the work you've done but I can't get the extraction to work. I can chose the date ranges from the dropdown boxes but no min/max loan figures populate in the dropdown boxes.

Would it be possible for the to/From date and Min/Max loan amounts to be user defined? meaning instead of being restricted to entering via a dropdown, you could enter any loan figures and any date range you wanted, this would allow for a greater amount of control over the reported data.

Again, what you've done so far is great and I feel guilty asking for more help.

 

[mumps]

Click here for your file. When you click on the date boxes, a calendar will pop up for you to choose a date. You can enter the loan figures manually. You can see the result using the criteria that are currently in the Priority KFI Cases sheet.