Error when calling a sub

[hazmat]

I have the following code , inserted within the Worksheet_Change event, and it works fine there.
However, if i make this exact code a stand alone sub routine, Private Sub Test(), and Call Test in the Worksheet_Change Sub, it gives me 'Run-time error 424: Object required, when the first IF Not Intersect.....line runs.
I don't understand why? Thanks

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("AF8:AF157")) Is Nothing Then
        Range("E8:E37").Value = Range("AF8:AF37").Value
        Range("I8:I37").Value = Range("AF38:AF67").Value
        Range("L8:L37").Value = Range("AF68:AF97").Value
        Range("O8:O37").Value = Range("AF98:AF127").Value
        Range("R8:R37").Value = Range("AF128:AF157").Value
    End If

    If Not Intersect(Target, Range("E8:E37,I8:I37,L8:L37,O8:O37,R8:R37")) Is Nothing Then
        Range("AF8:AF37").Value = Range("E8:E37").Value
        Range("AF38:AF67").Value = Range("I8:I37").Value
        Range("AF68:AF97").Value = Range("L8:L37").Value
        Range("AF98:AF127").Value = Range("O8:O37").Value
        Range("AF128:AF157").Value = Range("R8:R37").Value
    End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True

 

[mumps]

When using the variable "Target" in a Worksheet_Change event, the variable "target" is automatically recognized as the cell being changed without having to define it. When you use "Target" in a regular module, it is undefined so it produces an error. I would suggest using the Worksheet_Change event rather than calling the macro from the Worksheet_Change event.

 

[dmt32]

However, if i make this exact code a stand alone sub routine, Private Sub Test(), and Call Test in the Worksheet_Change Sub, it gives me 'Run-time error 424: Object required

If you have placed your code in a standard module then you will need to add a parameter so you can pass Target as an argument to it.

Something like this

Your code in standard module

Sub Test(ByVal Target As Range)

    On Error GoTo myerror
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Not Intersect(Target, Range("AF8:AF157")) Is Nothing Then
        Range("E8:E37").Value = Range("AF8:AF37").Value
        Range("I8:I37").Value = Range("AF38:AF67").Value
        Range("L8:L37").Value = Range("AF68:AF97").Value
        Range("O8:O37").Value = Range("AF98:AF127").Value
        Range("R8:R37").Value = Range("AF128:AF157").Value
    End If

    If Not Intersect(Target, Range("E8:E37,I8:I37,L8:L37,O8:O37,R8:R37")) Is Nothing Then
        Range("AF8:AF37").Value = Range("E8:E37").Value
        Range("AF38:AF67").Value = Range("I8:I37").Value
        Range("AF68:AF97").Value = Range("L8:L37").Value
        Range("AF98:AF127").Value = Range("O8:O37").Value
        Range("AF128:AF157").Value = Range("R8:R37").Value
    End If

myerror:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

and to call it from the change event

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Test(Target)
End Sub

Dave

 

[kvsrinivasamurthy]

Worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AF8:AF157")) Is Nothing Or Not Intersect(Target, Range("E8:E37,I8:I37,L8:L37,O8:O37,R8:R37")) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set Rng = Target
    Call Macro
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End If

    End Sub

Standard module

Option Explicit
Public Rng As Range

Sub Macro()

     If Not Intersect(Rng, Range("AF8:AF157")) Is Nothing Then

       Range("E8:E37").Value = Range("AF8:AF37").Value
        Range("I8:I37").Value = Range("AF38:AF67").Value
        Range("L8:L37").Value = Range("AF68:AF97").Value
        Range("O8:O37").Value = Range("AF98:AF127").Value
        Range("R8:R37").Value = Range("AF128:AF157").Value
    End If

    If Not Intersect(Rng, Range("E8:E37,I8:I37,L8:L37,O8:O37,R8:R37")) Is Nothing Then
        Range("AF8:AF37").Value = Range("E8:E37").Value
        Range("AF38:AF67").Value = Range("I8:I37").Value
        Range("AF68:AF97").Value = Range("L8:L37").Value
        Range("AF98:AF127").Value = Range("O8:O37").Value
        Range("AF128:AF157").Value = Range("R8:R37").Value
    End If
   
End Sub

 

[hazmat]

Thanks for the answers. I learned a bit more today

 

[dmt32]

Thanks for the answers. I learned a bit more today

most welcome glad we were able to assist & appreciate your feedback

Dave