VBA Code to copy data from one sheet to another based on date

Hi

I Hope someone can help.

I have a sheet (Sheet2) with data that has been completed throughout many years.

ID	NAME	DATE	AREA	EVENT
1	John	2018/02/22	East	Rate1
2	Dwayne	2016/05/05	East	Rate2
3	Jason	2017/06/01	West	Rate2
4	Frank	2014/04/04	South	Rate4

I need a code that will search the range (Sheet2 Column C "Dates") for any data exactly older than 365 days from a date entered in Sheet1 cell A1.

so if for example the date entered into sheet1 cell A1 was 2018/03/22 then the following data will appear on sheet3:

ID	NAME	DATE	AREA	EVENT
1	John	2018/02/22	East	Rate1
3	Jason	2017/06/01	West	Rate2

In the attached there's a button in Sheet1. Adjust the date in cell A1 and press the button.
it runs the following code:

Code:

---

Sub blah()
Sheets("Sheet2").Range("A1:E5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Sheet1").Range("D1:D2"), CopyToRange:=Sheets("Sheet3").Range("A1:E1"), Unique:=False
Sheets("Sheet3").Activate
End Sub

---

While this gives the results you cite, it isn't 'data exactly older than 365 days from a date entered in Sheet1 cell A1', it's data later than or equal to 365 days before the date in cell A1.

P45cal

After i copied your code into my workbook.

HTML Code:

---

Private Sub CommandButton2_Click()
Sheet3.Range("A1:G5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheet1.Range("AK2:AK3"), CopyToRange:=Sheet9.Range("A1:G1"), Unique:=False
Sheet9.Activate

---

I get run time error '1004' the extract range has a missing or invalid field name.

if I change this part

HTML Code:

---

Sheet3.Range("A1:G5")

---

to

HTML Code:

---

Sheet3.Range("A2:G5")

---

if the first line item is within 365 days it will only copy that first line. if not, i get the same error. for your information the date found is in column G. looks like this:

A	B	C	D	E	F	G
REF	CMP	CLASS	SEVERITY	RATE	AREA	Date
RSP01	RSP	Safety	Insignificant	5	Site01	2012/01/01
RSP02	RSP	Health	Moderate	20	Site02	2018/01/01
RS03	RSP	Environment	Insignificant	3	Site3	2018/01/01

Thanks for helping so far.

Maxicus,

Ensure the headings on the sheet you are copying to are the same as the sheet you are copying from.
Sheet 3 to Sheet 9.

BigDawg5