Extract specific data from 3 sheets to one sheet named "extraction"

[GotherExcel75]

Hello all,

I have to do this exercise:

Using the data contained in the workbook « Part 1 - Exercise1.xlsx » you must create a macro that will extract from the worksheets « D1 », « D2 » and « D3 » all the lines with a duration less than 08:00. Those lines must be copied in the worksheet « Extraction » in order to produce one single table. Before doing anything the macro must ensure that the destination worksheet is empty.
Add a button to the worksheet « Menu » in order to be able to trigger this macro.

It looks easy but I don't know how to filter the value with a duration less than 8:00 in VBA. I tried "AutoFilterMode" but it doesn't work..
My code for the Sheet1 :

Sub Extraction()
Dim My_Range As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim FilterCriteria As String
Dim rng As Range

Sheet1.Activate


Set My_Range = Sheet1.UsedRange.Select
My_Range.Parent.Select
My_Range.Parent.AutoFilterMode = False


My_Range.AutoFilter Field:=4, Criteria1:=">=8:00"


My_Range.Parent.AutoFilter.Range.Copy


Sheet4.Select
With Sheet4.Range("A1")
.PasteSpecial xlPasteValues

End With


End Sub





Thanks a lot for your Help !

 

[lrobbo314]

What is '8:00'? Is that a time or hours or minutes?

Posting some sample data would be helpful.

 

[GotherExcel75]

What is '8:00'? Is that a time or hours or minutes?

Posting some sample data would be helpful.

It's the participant's duration for a race. See example below, it's the beginning of my Excel file.
I hope it is helpful. Thank you very much

userID	Start	End	Duration	Amount
80	11:11:00	16:23	5:12	26,00 €
31	11:27:00	12:29	1:02	5,17 €
98	11:17:00	12:05	0:48	4,00 €
91	11:29:00	17:15	5:46	28,83 €
87	09:03:00	15:59	6:56	34,67 €

<colgroup><col width="73" span="5" style="width: 55pt;"></colgroup><tbody>
</tbody>

 

[lrobbo314]

The problem is that that duration is only formatted as a time. It's actually a decimal number in those cells. Since you are talking about filtering for values greater than or equal to 8 hours you need do divide 8 by 24 and that gives you the value to filter by as you can see in the code below.

Sub Extraction()
Dim My_Range As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim FilterCriteria As String
Dim rng As Range


Sheet1.Activate




Set My_Range = Sheet2.UsedRange
My_Range.Parent.Select
My_Range.Parent.AutoFilterMode = False
Dim dTime As Double
dTime = 8 / 24


My_Range.AutoFilter Field:=4, Criteria1:=">=" & dTime




My_Range.Parent.AutoFilter.Range.Copy




Sheet4.Select
With Sheet4.Range("A1")
.PasteSpecial xlPasteValues


End With




End Sub

 

[GotherExcel75]

The problem is that that duration is only formatted as a time. It's actually a decimal number in those cells. Since you are talking about filtering for values greater than or equal to 8 hours you need do divide 8 by 24 and that gives you the value to filter by as you can see in the code below.

Sub Extraction()
Dim My_Range As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim FilterCriteria As String
Dim rng As Range


Sheet1.Activate




Set My_Range = Sheet2.UsedRange
My_Range.Parent.Select
My_Range.Parent.AutoFilterMode = False
Dim dTime As Double
dTime = 8 / 24


My_Range.AutoFilter Field:=4, Criteria1:=">=" & dTime




My_Range.Parent.AutoFilter.Range.Copy




Sheet4.Select
With Sheet4.Range("A1")
.PasteSpecial xlPasteValues


End With




End Sub

Thank you very much! That works but it doesn't copy paste any data. Is it possible this duration is in minutes instead of hour ? How do I change the variable definition ?

 

[lrobbo314]

I was using a different sheet to test on. Change Set My_Range = Sheet2.UsedRange to Set My_Range = Sheet1.UsedRange and it should work.

 

[GotherExcel75]

I was using a different sheet to test on. Change Set My_Range = Sheet2.UsedRange to Set My_Range = Sheet1.UsedRange and it should work.

Thanks for you answer but it is what I did.
I have noticed your little change

 

[GotherExcel75]

Irobbot can you help me ?
I'm still struggling with the time format. When I look to format of the cells in the duration column, the format is: Time --> 13h30, as Sample : 5:12

I hope it's helpful.

Thank you very much