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:
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.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:
Upvote 0
Rather than copying the code to your file, you will have to copy the data from your file to the one I posted.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
@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?
 
Upvote 0
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.
 
Upvote 0
Thank you for that. :) Hopefully using cLng will fix the problem.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top