Retrieve all records from an EXCEL database by MAX value (Date) by using VBA code

[Mine Yours]

Hi. Could you please assist with coding to retrieve the following records by using VBA code? I can achieve this by using MAX excel function but would prefer using VBA code.

The database keeps a record of the service history of about 800 vehicles. What I would like to achieve is to retrieve the last service date of ALL vehicles in the database and put it in a new worksheet.

Example: (extraction)

Database: (Service History)

Week – Year Date Registration Kilometres Service Done​

48 – 2013 20 Nov 2013 AB 123456 78 945 A​

49 – 2013 27 Nov 2013 BC 789123 23 654 B​

03 – 2014 21 Jan 2014 AB 123456 88 000 B​

06 – 2014 13 Feb 2014 BC 789123 34 567 C​

20 – 2014 25 May 2014 AB 123456 100 000 C​

22 – 2014 6 June 2014 BC 789123 49 000 A​

This is what I would like to have in the :-

New Worksheet (Service Summation)

Week – Year Date Registration Kilometres Service Done​

20 – 2014 25 May 2014 AB 123456 100 000 C​

22 – 2014 6 June 2014 BC 789123 49 000 A​

Any assistance will be greatly appreciated

 

[Smallman]

Hi MY

Welcome to the forum. Your post is a little difficult to understand. At the first instance you are asking for a max value then it looks like you wish to see some sort of summation. Without data from the same week in your example it is difficult to tell what the trigger is to put the data in the Service Summation tab. Week 20/22 is not the highest week and there is no way to tell if the data in the new worksheet is a summation.

Can you provide a bit more clarity perhaps using a file with dummy data?

Ta

Smallman

 

[Mine Yours]

Hi Smallman

I am going to use a Button to trigger the extraction from the "Service History" database. The extraction (summation) that I would like to achieve is this:- there are +/-700 vehicles in the fleet, each with its unique REGISTRATION number e.g.(AB 123456) . When the button is pressed the code must look for the last date (e.g. 20 June 2014 ) that each unique registration (vehicle) was serviced and extract the last service for each vehicle in the fleet and put it into the "Service Summation" worksheet. The final result is that the "Service Summation" worksheet will have all vehicles that is in the "Service History" worksheet in it and indicating when those vehicle have last been serviced. I have attached a file and I hope that you will understand what I would like to achieve.

Thanks
Mine

 

[Smallman]

Hi MY

I would set it up as follows. Create a Advanced filter which pulls the unique numbers. Then I would tie formula in to grab the data you need based on the maximum service data. I have set the attached file up for you. Here is the code.

Option Explicit
 
Sub GetData()
Dim lr As Long
 
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row 'Sh2 = Svs History
Sheet2.Range("E1:E" & lr).AdvancedFilter 2, , [J5], True
Range("A6:G" & Range("E" & Rows.Count).End(xlUp).Row).FillDown
End Sub

I have attached a file to show workings.

Take care

Smallman

 

[Mine Yours]

Hi Smallman

Thank you very much . The code achieves what i want. Is there perhaps a method where all the coding is done in the background and leaves nothing to tamper with in the cells. To prevent the tampering I would have to protect the sheet.
However, the code works and does what i want.
As I am a novice at VBA, would you mind explaining the part of the code that is highlighted in red pasted below:-

Sheet2.Range("E1:E" & lr).AdvancedFilter 2, , [J5], True

Once again, Thanks
Mine

 

[Smallman]

Hi MY

Sorry for the slow response. I don't quite understand your first question. It looks to me like the code runs in the background. Unless you mean you want all of the formulas in VB and in that case you will need to unprotect, perform an action, then protect again. Can you clarify?

As for this part;

Sheet2.Range("E1:E" & lr).AdvancedFilter 2, , [J5], True
The Advanced filter in VB offers two choices for filtering;

Filter in Place
Filter Copy

Filter Copy is the second item in the list. As Excel is a binary language you can use the number 2 as the 2 stands for xlFilterCopy which is the second item in the list. I usually use this method to ensure the line does not run over two lines in VB. It does not make it easy to read I will grant you that but I understand what the numbers are doing and I use them extensively.

[J5] just stands for the Range, Range("J5") and this is where the filtered list will be transposed to.

Finally the True is the last question the Advanced filter asks which is Are you looking for Unique Records? This is a true false answer and the answer is True (1 can also be used in this case).

Hope this explanation helps.

Take care

Smallman