Snasui Excel Forum

Code: Select all

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim rngA As Range

    Dim lRec As Long
    Dim lRecRow As Long
    Dim lLastRec As Long
    Dim lastRow As Long

    Set rngA = ActiveCell

    If Target.Address = Me.Range("CurrRec").Address _
        Or Target.Address = Me.Range("EnSel").Address Then
      Application.EnableEvents = False
      
      If Target.Address = Me.Range("EnSel").Address Then
        Me.Range("CurrRec").Value = Me.Range("SelRec").Value
      End If

      Set inputWks = Worksheets("DataEntry")
      Set historyWks = Worksheets("TNrecordData")
  
      With historyWks
          lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
          lLastRec = lastRow - 1
      End With
  
      With inputWks
          lRec = .Range("CurrRec").Value
          If lRec > 0 And lRec <= lLastRec Then
                lRecRow = lRec + 1
                historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 16)).Copy
                .Range("G9").PasteSpecial Paste:=xlPasteValues, Transpose:=True
                rngA.Select
        End If
      End With
      Application.EnableEvents = True
    End If

End Sub

Code: Select all

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 2 Then
  If Target.Value = "" Then GoTo exitHandler
  Application.EnableEvents = False
  Target.Value = Worksheets("Codes").Range("C1") _
    .Offset(Application.WorksheetFunction _
    .Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

errHandler:
  If Err.Number = 13 Or Err.Number = 1004 Then
    GoTo exitHandler
  Else
    Resume Next
  End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1
    Call Macro2
    Call Macro3
    '....
End Sub