Move vba Sub in sheet to activate on change

[oblix]

Hi there
I have this code I would like clean up
Done mostly with macro recorder (and I know it sucks!)
and then to place in sheet "PERMENANT LIVING IN REGISTER" to run on changes that take place in this sheet

Sub FILTER_PERMEXPORT()

Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("Perm Transfer").Select
Range("O3:Z1000").Select
Selection.ClearContents

Sheets("PERMENANT LIVING IN REGISTER").Range("C4:AV500").AdvancedFilter Action _
:=xlFilterCopy, CriteriaRange:=Range("'Perm Transfer'!Criteria_P"), _
CopyToRange:=Range("'Perm Transfer'!Extract_P"), Unique:=False
Call Sort_perm2

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

please assist

 

[Micron]

copy all between Sub and End Sub, choose worksheet from the top left vb editor dropdown, choose an event from the right dropdown (possibly Change or SelectionChange) and paste in the resulting sub?

 

[oblix]

Thank you for reply

but my codes is realy a mess
I tried that but then gives me error because of this:

Sheets("Perm Transfer").Select
Range("O3:Z1000").Select
Selection.ClearContents
I think at least it is the problem
Not sure that it is efficient or should be there at all?

 

[Micron]

You're being rather cryptic by not showing all of the new code and especially by not saying what the error message is. Doubly so by posting multiple lines and not saying which one raises the error. I'd rather look at all the 'messy' code as opposed to bits and pieces that don't reveal much at all.
When posting more than a few lines of code, please use code tags (VBA button on posting toolbar) to maintain indentation and readability.

 

[oblix]

Sorry, I have changed the code to look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    ThisWorkbook.Worksheets("Perm Transfer").Range("O3:Z1000").ClearContents
    
    Sheets("PERMENANT LIVING IN REGISTER").Range("C4:AV500").AdvancedFilter Action _
                      :=xlFilterCopy, CriteriaRange:=Range("'Perm Transfer'!Criteria_P"), _
                      CopyToRange:=Range("'Perm Transfer'!Extract_P"), Unique:=False
    
    Call Sort_perm2
    
    Range("O3").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
End Sub

and this part is flaged in yellow:
Sheets("PERMENANT LIVING IN REGISTER").Range("C4:AV500").AdvancedFilter Action _
:=xlFilterCopy, CriteriaRange:=Range("'Perm Transfer'!Criteria_P"), _
CopyToRange:=Range("'Perm Transfer'!Extract_P"), Unique:=False

Can It By because I am trying to place this code in the ....Sheets("PERMENANT LIVING IN REGISTER") it self?

 

[Micron]

You're being rather cryptic by not showing all of the new code and especially by not saying what the error message is

I'm going to guess that the problem is your range syntax (use of single quotes). Try

Range("Perm Transfer!Criteria_P"),
and
Range("Perm Transfer!Extract_P"), Unique:=False