Help needed with macro!!!

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:
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
 

Forum statistics

Threads
1,077,928
Messages
5,337,256
Members
399,134
Latest member
ChetManley

Some videos you may like

This Week's Hot Topics

Top