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

VonHelson

New Member
Joined
Nov 13, 2018
Messages
15
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 -

VBA Code:
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Nov 13, 2018
Messages
15
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
I wouldn't need all the rows, 8 or 10 rows would be enough.
 

VonHelson

New Member
Joined
Nov 13, 2018
Messages
15

ADVERTISEMENT

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
ABCDEFGHIJKLM
1Account NumberCustomers NamesTotalApplicantsStatusNameIdBroker NameMaxLoanAmountInitialLoanAmountRequestedCustomerRequestedFacilityLoanToValueVariantCodeProductCategoryProductNameApplicationDate
24486486Davis1CancelledDeclinedTerence Harford845,000.00140,000.0021,000.00N/A1Cat 1Lending 116/09/2019
34684646Church2KFICompleteDavid McDaniels451,222.00400,000.00Lump SumN/A2Cat 5Lending 114/10/2019
446454McAfee2CompletedFoine McAnn18,466.00250,500.00Lump SumN/A3Cat 7Lending 120/05/2019
548949Saheed2KFICompleteJason Eldridge15,487.0040,000.0060,750.00N/A4Cat 1Lending 108/09/2020
63843854Sheldon1CompletedHery Wainscott45,118.0020,000.0025,050.00N/A5Cat 3Lending 130/09/2019
71896186Kurts2KFICompleteHarriet Edwards154,848.00100,000.00200,000.00N/A6Cat 2Lending 109/07/2020
83845348Mills2KFIProgressRichard Smith78,954.00100,000.00Lump SumN/A7Cat 2Lending 115/03/2020
978643Nelson2CancelledDeclinedJerry Porter3,518,161.0075,000.00Lump SumN/A8Cat 6Lending 115/05/2019
101864684Trevors2CancelledDeclinedOscar Nunez7,815,316.00200,000.00110,000.00N/A9Cat 7Lending 103/07/2019
11638685Rochards2CompletedMatt Goddard4,568,788.0025,000.0035,000.00N/A10Cat 1Lending 107/02/2020
121268438Smith2CompletedLiam Gist15,154.0085,000.00205,000.00N/A11Cat 1Lending 121/05/2020
13386816Nigels1CompletedNatasha Gunt254,816.0012,400.0060,100.00N/A12Cat3Lending 118/02/2020
Sheet1
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Nov 13, 2018
Messages
15

ADVERTISEMENT

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
BCDEFGHIJKLMNOPQ
4
5
6Priority Data
7
8
9
10
11
12KFI Count1193To DateFrom Date
13
14
15Min Loan AmountMax Loan AmountAdvisor
16
17
18
19
20
21
22
23
24
Priority KFI cases
Cell Formulas
RangeFormula
E12E12=COUNTIF('Raw Data Sheet'!D:D,"*KFIComplete*")
Named Ranges
NameRefers ToCells
'Raw Data Sheet'!_FilterDatabase='Raw Data Sheet'!$D$1:$D$4512E12:F12
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Nov 13, 2018
Messages
15
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,976
Members
416,003
Latest member
indyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top