GotherExcel75
New Member
- Joined
- Oct 8, 2018
- Messages
- 5
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 !
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 !