Steffmeister
Board Regular
- Joined
- Nov 7, 2005
- Messages
- 195
Hi guys,
I got a question about my macro.
The macro filter some cells from sheet1 which contains 'dyn', 'sig-', 'Hursley' and 'DEIBP9EH1' and put them in sheet 2.
Now I want to copy the rest of the cells of sheet1 to sheet2. Is that possible?
My code what I've got now is:
Grts. Steffmeister
I got a question about my macro.
The macro filter some cells from sheet1 which contains 'dyn', 'sig-', 'Hursley' and 'DEIBP9EH1' and put them in sheet 2.
Now I want to copy the rest of the cells of sheet1 to sheet2. Is that possible?
My code what I've got now is:
Code:
Sub Filter()
'
' Data to be filtered is in Sheet1 ... cells A1 down
Sheets("sheet2").Activate
'incorporating Filter Criterion
Sheets("sheet2").Range("B2").Formula = "=(FIND(""dyn"",A2))"
Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=False
'data with dyn's will be copied to column B of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
Sheets("sheet2").Range("a1", Range("a1").End(xlDown)).Copy [sheet1!B1]
'DELETE CELLS FROM SHEET 1 WHICH ARE IN SHEET 2
' Data to be filtered is in Sheet1 ... cells A1 down
Sheets("sheet2").Activate
'incorporating Filter Criterion
Sheets("sheet2").Range("C2").Formula = "=(FIND(""sig-"",A2))"
Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("C1:C2"), CopyToRange:=Sheets("Sheet2").Range("B1"), Unique:=False
'data with Non-existent 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("B1", Range("B1").End(xlDown)).Copy [sheet1!C1]
'DELETE CELLS FROM SHEET 1 WHICH ARE IN SHEET 2
' Data to be filtered is in Sheet1 ... cells A1 down
Sheets("sheet2").Activate
'incorporating Filter Criterion
Sheets("sheet2").Range("D2").Formula = "=(FIND(""hursley"",A2))"
Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("D1:D2"), CopyToRange:=Sheets("Sheet2").Range("C1"), Unique:=False
'data with hursley will be copied to column D of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
Sheets("sheet2").Range("D1", Range("D1").End(xlDown)).Copy [sheet1!D1]
'DELETE CELLS FROM SHEET 1 WHICH ARE IN SHEET 2
' Data to be filtered is in Sheet1 ... cells A1 down
Sheets("sheet2").Activate
'incorporating Filter Criterion
Sheets("sheet2").Range("E2").Formula = "=(FIND(""DEIBP9EH1"",A2))"
Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("E1:E2"), CopyToRange:=Sheets("Sheet2").Range("D1"), Unique:=False
'data with hursley will be copied to column D of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
Sheets("sheet2").Range("E1", Range("E1").End(xlDown)).Copy [sheet1!E1]
'DELETE CELLS FROM SHEET 1 WHICH ARE IN SHEET 2
End Sub
Grts. Steffmeister