Excell - VBA - copying form one worksheet to other

[Evening]

I have a 2 worksheets, FMEA, and RPN_ORDER. I want to copy all fields A10:U1000 from worksheet FMEA, to worksheet RPN_ORDER. When I made a Macro and run it alone, just for the copying and pasting, it is working. When I try to use same in the program below, the program always stops at line I marked below, with run time error (Range(“A10:U1000”) ). I just don’t know why would be running wit standalone Macro, and not in VBA program.


Private Sub Worksheet_Change(ByVal num As Range)
If num.Address <> Range("num").Address Then Exit Sub
cursht = ActiveSheet.Name
curcell = ActiveCell.Address
'------------------------------'
Range("1:65536").Select
Selection.EntireRow.Hidden = False
Range("A:IV").Select
Selection.EntireColumn.Hidden = False
'----------------------------------------------------'

Sheets("FMEA").Select
-> Range("A10:U1000").Select
Selection.Copy
Sheets("RPN_ORDER").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'-----------------------------------------------------'
If Worksheets("RPN_ORDER").FilterMode = True Then
ActiveSheet.ShowAllData
Selection.AutoFilter
Else
End If

Select Case num

Case Is > 1
Rows("10:999").Select
Selection.Sort Key1:=Range("N10"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range(num + 10 & ":999").Select
Selection.EntireRow.Hidden = True

Range("1001:65536").Select
Selection.EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$2:$V$1000"

Case else
Rows("10:999").Select
Selection.Sort Key1:=Range("N10"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("Y10:Y1000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
End Select
End Sub

 

[SkipVought]

Hi,

Activate and Select ought to be used very little. First it slows down your code. AS you have been doing, explicitly define you ranges with Sheet references.

Private Sub Worksheet_Change(ByVal num As Range)
    If num.Address <> Range("num").Address Then Exit Sub
    cursht = ActiveSheet.Name
    curcell = ActiveCell.Address
    '------------------------------'
    Range("1:65536").EntireRow.Hidden = False
    Range("A:IV").EntireColumn.Hidden = False
    '----------------------------------------------------'
    
    Sheets("FMEA").Range("A10:U1000").Copy
    Sheets("RPN_ORDER").Range("A10").PasteSpecial _
        Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
    '-----------------------------------------------------'
    With Worksheets("RPN_ORDER")
        If .FilterMode = True Then
          .ShowAllData
          .[A1].AutoFilter
        Else
        End If
        
           Select Case num
                     
             Case Is > 1
                .Rows("10:999").Sort Key1:=Range("N10"), Order1:=xlDescending, Header:=xlGuess _
                , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
                
                .Range(num + 10 & ":999").EntireRow.Hidden = True
                                            
                .Range("1001:65536").EntireRow.Hidden = True
                .PageSetup.PrintArea = "$A$2:$V$1000"
    
            Case Else
                .Rows("10:999").Sort Key1:=Range("N10"), Order1:=xlDescending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
    
                .Range("Y10:Y1000").AutoFilter
                .[Y10].AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
           End Select
    End With
End Sub

check out How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Evening]

Thank you Skip, it's working now