PDA

View Full Version : [SOLVED] VBA Code to copy data from one sheet to another based on date



Maxicus
03-22-2018, 04:46 AM
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

p45cal
03-22-2018, 11:06 AM
In the attached there's a button in Sheet1. Adjust the date in cell A1 and press the button.
it runs the following 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.

Maxicus
03-22-2018, 10:39 PM
P45cal

After i copied your code into my workbook.


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

Sheet3.Range("A1:G5")
to

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.

BigDawg15
04-11-2018, 09:32 AM
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