Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Macro
Hi,
I am using the below macro to filter for data between two dates. I there a way that the code can be adpted so that the start data and en date only needs to be entered once and can then take extracts for thos values from all worksheets\books Dim rng As Range Dim rng2 As Range Dim strStart As String Dim strEnd As String GetForm: Sheets("extract").Visible = True Sheets("Purchasing").Select strStart = InputBox("Please enter start date, (dd/mm/yyyy)", "Tur Around checker: Start Date") strEnd = InputBox("Please enter end date, (dd/mm/yyyy)", "Turn Aroun Checker: End Date") Selection.AutoFilter Field:=2, Criteria1:="=" CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With Worksheets("extract").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Copy Destination:=Worksheets("extract").Range("A1" -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Criteria Macro
Hi
one way would be the use of two cells in your workbook. lets say A1 and A2 on shet one. Change the lines strStart = InputBox("Please enter start date, (dd/mm/yyyy)", "Turn Around checker: Start Date") strEnd = InputBox("Please enter end date, (dd/mm/yyyy)", "Turn Around Checker: End Date") to strStart = Activeworkbook.worksheets("Sheet1").range("A1").va lue strEnd = Activeworkbook.worksheets("Sheet1").range("A2").va lue -- Regards Frank Kabel Frankfurt, Germany "pauluk " schrieb im Newsbeitrag ... Hi, I am using the below macro to filter for data between two dates. Is there a way that the code can be adpted so that the start data and end date only needs to be entered once and can then take extracts for those values from all worksheets\books Dim rng As Range Dim rng2 As Range Dim strStart As String Dim strEnd As String GetForm: Sheets("extract").Visible = True Sheets("Purchasing").Select strStart = InputBox("Please enter start date, (dd/mm/yyyy)", "Turn Around checker: Start Date") strEnd = InputBox("Please enter end date, (dd/mm/yyyy)", "Turn Around Checker: End Date") Selection.AutoFilter Field:=2, Criteria1:="=" & CLng(CDate(strStart)), Operator:=xlAnd, _ Criteria2:="<=" & CLng(CDate(strEnd)) With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With Worksheets("extract").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Copy Destination:=Worksheets("extract").Range("A1") --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a macro with finishing criteria | Excel Discussion (Misc queries) | |||
Macro copy with criteria | Excel Discussion (Misc queries) | |||
MACRO: Add formula's across row if criteria is met | Excel Discussion (Misc queries) | |||
Can a macro erase it's self after a set criteria? | Excel Worksheet Functions | |||
Start and end criteria macro | Excel Programming |