Search data between two date copy to another sheet.

[aamir]

I have a Master_sheet with lot of data column D is DATE ([$-409]d-mmm-yy;@) & column G is (=D-today()). In O1 date ([$-409]d-mmm;@) & R1 date (=DATE(YEAR(O1),MONTH(O1)+3,DAY(O1))-1).
I want is when i date any date from O1 (drop down list). Only column A, B, D, E & F data which falls between these two dates (O1 & R1 should be displayed to sheet2?
How i do it? macro!!

https://hotfile.com/dl/161189639/8ce4ca0/Report.xlsx.html

 

[xchaser]

try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim FinalRow As Long
Dim WSA, WSMS As Worksheet
Dim i, j As Integer
Set WSA = Worksheets("Majid Report")
Set WSMS = Worksheets("Master_Sheet")
FinalRow = WSMS.Cells(Rows.Count, 3).End(xlUp).Row
WSA.Range("A3:Z1000").ClearContents
j = 3
For i = 2 To FinalRow + 5
If WSMS.Range("D" & i).Value >= WSMS.Range("O$1").Value Then
If WSMS.Range("D" & i) <= WSMS.Range("R$1") Then
WSMS.Cells(i, 1).Resize(1, 2).copy Destination:=WSA.Cells(j, 1)
WSMS.Cells(i, 4).Resize(1, 3).copy Destination:=WSA.Cells(j, 3)
j = j + 1
Else
End If
Else
End If
Next i
FinalRow = WSA.Cells(Rows.Count, 3).End(xlUp).Row
With WSA
.Cells(3, 1).Resize(FinalRow, 6).FormatConditions.Delete
.Columns("A:E").AutoFit
End With
End Sub

 

[aamir]

Thanks and what if if i want same data on sheet "Aamir" not based on this date but which is less than 90 from column G in Master Sheet ?

 

[xchaser]

maybe try this

Sub Aamir()
Dim FinalRow As Long
Dim WSB, WSMS As Worksheet
Dim i, j As Integer
Set WSB = Worksheets("Aamir")
Set WSMS = Worksheets("Master_Sheet")
FinalRow = WSMS.Cells(Rows.Count, 7).End(xlUp).Row
WSB.Range("A3:Z1000").ClearContents
j = 3
For i = 2 To FinalRow
If WSMS.Range("G" & i).Value < 90 Then
WSMS.Cells(i, 1).Resize(1, 2).copy Destination:=WSB.Cells(j, 1)
WSMS.Cells(i, 4).Resize(1, 3).copy Destination:=WSB.Cells(j, 3)
j = j + 1
Else
End If
Next i
FinalRow = WSB.Cells(Rows.Count, 3).End(xlUp).Row
With WSB
.Cells(3, 1).Resize(FinalRow, 6).FormatConditions.Delete
.Columns("A:E").AutoFit
End With
End Sub

incorporate it into the previous public sub if you want it also to run every time "Master Sheet" is changed

 

[aamir]

Thank you so much.