I have the next VBA code that take to dates and filter all relevant data according to them + move it to new sheet.
I want to improve my code and change it to display view instead of static cells at excel sheet (you may see below how the input data looks now).
<code>Start time filter: 5/20/2018 13:00
End time filter: 5/22/2018 13:00</code>what I hope to see is--> after I click on Macro sign I want to get a new view display, there I want to see 3 filter options: 1. start time (can be always 08:00 and date can be chose from month view dates). 2. End time (as start time) after both of them were set all retrieved data should be according to this time frame. 3. Drop down filter on "Product" column (available in my excel).
My current VBA code:
Thanks!!
I want to improve my code and change it to display view instead of static cells at excel sheet (you may see below how the input data looks now).
<code>Start time filter: 5/20/2018 13:00
End time filter: 5/22/2018 13:00</code>what I hope to see is--> after I click on Macro sign I want to get a new view display, there I want to see 3 filter options: 1. start time (can be always 08:00 and date can be chose from month view dates). 2. End time (as start time) after both of them were set all retrieved data should be according to this time frame. 3. Drop down filter on "Product" column (available in my excel).
My current VBA code:
Code:
<code>Public Sub MyFilter()
Dim lngStart As Date, lngEnd As Date
lngStart = Range("b2").Value 'assume this is the start date
lngEnd = Range("b3").Value 'assume this is the end date
Range("q:q").AutoFilter field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
Range("A1:s3000").Select
Range("A:A").Activate
Selection.Copy
Sheets.Add After:=ActiveSheet
With ActiveSheet
.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Columns("A:A").EntireColumn.AutoFit
.Cells.Select
.Cells.EntireColumn.AutoFit
.Rows("1:1").Select
.Application.CutCopyMode = False
With Selection
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
.AutoFilter
Columns("Q:Q").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
End With
.Columns("A:A").EntireColumn.AutoFit
.Range("A2").Select
End With</code>
Last edited by a moderator: