Filter data

[Steffmeister]

Hi guys,

I have data in Excel and want to filter cells which contains 'dyn'.
The cells with the name dyn in it must copied in sheet 2. In sheet1 must those addresses deleted. Must this be done with a macro?
Can somebody help me?

Tnx for now

Steffmeister

 

[Yogi Anand]

Hi Steffmeister:

The following macro will filter the data in column A of Sheet1 for dyn and hursley, copy theseto cell A5 of Sheet2 and cell C1 of Sheet1

Sub ySteff()
' Data to be filtered is in Sheet1 ... cells A1 down
    Sheets("sheet2").Activate
'incorporating Filter Criterion
    Sheets("sheet2").Range("c2").Formula = "=FIND(""dyn"",A2)"
    Sheets("sheet2").Range("C3").Formula = "=FIND(""hursley"",A2)"
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("C1:C3"), CopyToRange:=Sheets("Sheet2").Range("A5"), Unique:=False
    'data without dyn's will be copied to column C of Sheet1 (it can of course be copied to column A ...
    'I want to retain the original data ... just in case
    Sheets("sheet2").Range("a5", Range("a5").End(xlDown)).Copy [sheet1!C1]
End Sub

In the following illustration, the data to be filtered is in column A of Sheet1 ...

y051113h1.xls
	A	B	C	D
1	Field1		Field1
2	jin		dyn
3	min		dyn
4	dyn		hursley
5	bin
6	flyn
7	raoul
8	beth
9	dyn
10	floe
11	hursley
12	ben
13
Sheet1

Following is Sheet2 with C1:C3 as the criteria range, and the data filtered from Sheet2 is displayed in cell A5 and down

y051113h1.xls
	A	B	C	D
1
2			#VALUE!
3			#VALUE!
4
5	Field1
6	dyn
7	dyn
8	hursley
9
10
11
12
13
Sheet2

 

[Steffmeister]

Hi Yogi Anand,

Tnx for the nice macro .
Now I got my last question . Is it possible to delete the files in sheet 1 which are copied to sheet 2?

Tnx. Grts. Steffmeister

 

[Yogi Anand]

Hi Yogi Anand,

Tnx for the nice macro .
Now I got my last question . Is it possible to delete the files in sheet 1 which are copied to sheet 2?

Tnx. Grts. Steffmeister

Hi steffmeister:

Do you mean to delete the records from column A of Sheet1? If that is what you mean, please note I copied the records that were extracted to Sheet2 to column C of Sheet1.

I don't recommend it, but you can copy the extracted records to column A of Sheet1 ... by adjusting the code as in the following ...

Sub ySteff()
' Data to be filtered is in Sheet1 ... cells A1 down
    Sheets("sheet2").Activate
'incorporating Filter Criterion
    Sheets("sheet2").Range("c2").Formula = "=FIND(""dyn"",A2)"
    Sheets("sheet2").Range("C3").Formula = "=FIND(""hursley"",A2)"
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("C1:C3"), CopyToRange:=Sheets("Sheet2").Range("A5"), Unique:=False
    'data with dyn and hursley will be copied to column A of Sheet1 (it can of course be copied to column A ...
    'I want to retain the original data ... just in case
    Range("a5:a65536").Copy [Sheet1!A1]
End Sub

Is this what you are looking for?