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

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/