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:

VonHelson

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

Hi Mumps,

I've been trying to get this to work to no avail.

Regardless of what's input, it'l keep stating "No Data to Copy, Please check Criteria."

It'll do this even if I know I'm selecting a range that specifically relates to a case.
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

VonHelson

New Member
Joined
Nov 13, 2018
Messages
15
I tried to copy across your code onto my spreadsheet and it kept on giving me Object Required errors for calendarfrm.sho and also Method 'Range" of object'_Global" failed
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Rather than copying the code to your file, you will have to copy the data from your file to the one I posted.
 

VonHelson

New Member
Joined
Nov 13, 2018
Messages
15
Rather than copying the code to your file, you will have to copy the data from your file to the one I posted.

I've tried this but it still keep on saying "No Data to Copy, Please check Criteria."

It'll do this even if you select criteria that does match the raw data.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238

ADVERTISEMENT

As you were able to see in the sample file I posted, the macro does work. Most often what works on a sample file doesn't work on the actual file. Without seeing the actual file, it's difficult to diagnose the problem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you use UK style dates try
VBA Code:
        .Cells(1, 1).AutoFilter 13, Criteria1:=">=" & CLng(Range("K13").Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Range("H13").Value)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
@Fluff
I thought that the date format might be an issue but I wasn't sure.
The macro in the file I uploaded does work for me. Using the criteria shown, each time I click the button, the same two rows of data are added. Is that not the case when you tried it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
No I get the same error as the OP reported.
Dates in VBA can be a PITA for anyone who does not use US date formats.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Thank you for that. :) Hopefully using cLng will fix the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,972
Members
416,002
Latest member
Neshx

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