Experts Exchange connects you with the people and services you need so you can get back to work.
Dim WS As Worksheet
Dim WSResult As Worksheet
Dim sDate As String, eDate As String
Dim MaxRow As Long
If MsgBox("This process will lookup all sheets in this workbook for a date range in Col A and will return in this sheet all the rows where the data falls in between the selected dates." & Chr(10) & Chr(10) _
& "Are you ready to proceed ?", vbQuestion + vbYesNo, "Get Data") = vbYes Then
Set WSResult = Sheets("Results")
'---> Start Date
sDate = InputBox("Input starting Date: ", "Start Date", Format(Now, "mm/dd/yyyy"))
Loop Until IsDate(sDate)
'---> End Date
eDate = InputBox("Input ending Date: ", "End Date", Format(Now, "mm/dd/yyyy"))
Loop Until IsDate(eDate)
For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "Results" Then
'---> Filter the data as per dates
WS.UsedRange.AutoFilter field:=1, Criteria1:=">=" & sDate, Operator:=xlAnd, Criteria2:="<=" & eDate
If WS.Rows.End(xlDown).Row < 1048576 Then
'---> first time put the header
If MaxRow = 0 Then
MaxRow = 2
WS.Range("A2", "A" & WS.Rows.End(xlDown).Row).EntireRow.SpecialCells(xlCellTypeVisible).Copy
WSResult.Range("A" & MaxRow).PasteSpecial
MaxRow = WSResult.UsedRange.Rows.Count + 1
'---> Advise user
If MaxRow - 2 = 0 Then
MsgBox ("No rows were found in the date range " & sDate & " - " & eDate)
MsgBox ("A total of " & MaxRow - 2 & " rows were found in the date range " & sDate & " - " & eDate & " and copied successfully.")
Open in new window
Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Premium members get this course for $79.20.
Premium members get this course for $122.40.
Premium members get this course for $159.20.
Premium members get this course for $174.99.
Premium members get this course for $99.99.
Premium members get this course for $299.99.