Put Two Worksheet_Change in one Sheet

[Khalid1990]

Hi all, Please Help Me to Put this two Worksheet_Change To one Sheet:

- First one:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
If Not Intersect(Target, Range("R6:R1000000")) Is Nothing Then
Hyperlinks.Add anchor:=Target, Address:=Target.Value
End If
End Sub

- Second One:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).FormulaR1C1 = "=ROW()-5"
ElseIf Target.Column = 2 And Target.Offset(0, -1).IsEmpty = "TRUE" Then
End If
End Sub

Thanks>>>

 

[Khalid1990]

this is the Second one with on Error Resume Next:

Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 And Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).FormulaR1C1 = "=ROW()-5"
ElseIf Target.Column = 2 And Target.Offset(0, -1).IsEmpty = "TRUE" Then
End If
End Sub

 

[mumps]

Try:

Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B,R:R")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 2
            If Target.Offset(0, -1).Value = "" Then
                Target.Offset(0, -1) = Target.Row - 5
            End If
        Case Is = 18
            If Not Intersect(Target, Range("R6:R1000000")) Is Nothing Then
                Hyperlinks.Add anchor:=Target, Address:=Target.Value
            End If
    End Select
    Application.EnableEvents = True
End Sub

 

[Khalid1990]

run time error 13

Type Mismatch

on this code: If Target.Offset(0, -1).Value = "" Then

 

[Khalid1990]

plesae note that i transfer the data with this code:

Sub a()
X = Range("I4").Value
Sheets("Main1").Activate
Sheets(X).Unprotect Password:="KhExc2018"
Range("A11:V11" & Cells(Rows.Count, "b").End(xlUp).Row).Copy
Sheets(X).Activate
Lastrow = Range("b" & Rows.Count).End(xlUp).Row
Range("b" & Lastrow + 1).Select
Selection.PasteSpecial xlPasteValues


Range("K11" & Cells(Rows.Count, "b").End(xlUp).Row).Copy
Sheets(X).Activate
Lastrow = Range("b" & Rows.Count).End(xlUp).Row
Range("b" & Lastrow + 1).Select
Sheets(X).Paste

Sheets(X).Protect Password:="KhExc2018", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
Sheets("Main1").Activate
Range("I4").Select
Application.CutCopyMode = False
End Sub

 

[Khalid1990]

So i Have 2 sheets:

The first one is to transfer data to another data.

the transfer data code is in the first sheet.

the two worksheet_change codes is in the second sheet.

Thanks

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Khalid1990]

*the first one is to transfer data to another sheet

 

[mumps]

Please upload a copy of your file as requested in Post #7 .