Run macro on event

[mpatino]

Hi guys,

I have the working code below, however when I try to added to run in a change event the Range with lastrow stops working, wondering if any of you can advice on what can I do?


Sub field_change()

Dim LastRow As Integer

With ActiveSheet
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
Range("L" & LastRow).Select

If ActiveCell = "Dev & QA" Then
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False

With ActiveSheet
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
Range("L" & LastRow).Offset(0, 1).Select
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
End With

With ActiveSheet
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
Range("M" & LastRow).Offset(0, 1).Select
ActiveCell.Value = Application.UserName
End With

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Select
End With
Else
If ActiveCell = "Production" Then

Range("L" & LastRow).Select
' ' MsgBox "Production"
ActiveCell.EntireRow.Copy
Worksheets("Prod").Select
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Select
ActiveCell.Offset(1).Select
ActiveSheet.Paste

With ActiveSheet
LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
Range("L" & LastRow).Offset(0, 1).Select
ActiveCell.Value = Format(Now, "mm/dd/yyyy")
End With

With ActiveSheet
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
Range("M" & LastRow).Offset(0, 1).Select
ActiveCell.Value = Application.UserName
End With

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow).Select
End With

End With



End If

End If



End With

End Sub

 

[t0ny84]

Hi mpatino,

Have you tried having your macro as it is but adding in something like below to call your macro when the change occurs?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
call field_change()

End Sub

 

[mpatino]

Yes I did try that, I just got my first if to work, but still trying to figure out why after the Else I would still have issues:

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.

Dim LastRow As Integer
'1
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
    '2
    If Not Intersect(Target, Range("L" & LastRow)) Is Nothing Then
'Target.EntireRow.Interior.ColorIndex = 15
        '3
        If ActiveCell = "Dev & QA" Then
               ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
               ActiveCell.EntireRow.Copy
               ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
               Application.CutCopyMode = False

        With ActiveSheet
              LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
              Range("L" & LastRow).Offset(0, 1).Select
              ActiveCell.Value = Format(Now, "mm/dd/yyyy")
        End With

        With ActiveSheet
               LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
               Range("M" & LastRow).Offset(0, 1).Select
               ActiveCell.Value = Application.UserName
        End With

        With ActiveSheet
               LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
               Range("A" & LastRow).Select
        End With

            Else

        '4
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row

            '5
              If Not Intersect(Target, Range("L" & LastRow)) Is Nothing Then
                '6
                If ActiveCell = "Production" Then
                        Range("L" & LastRow).Select
'                    ' MsgBox "Production"
                        ActiveCell.EntireRow.Copy
                        Worksheets("Prod").Select


                    '7
                    With ActiveSheet
                    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                    Range("A" & LastRow).Select
                    ActiveCell.Offset(1).Select
                    ActiveSheet.Paste

                        With ActiveSheet
                             LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
                             Range("L" & LastRow).Offset(0, 1).Select
                             ActiveCell.Value = Format(Now, "mm/dd/yyyy")
                        End With
                        With ActiveSheet
                              LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
                              Range("M" & LastRow).Offset(0, 1).Select
                              ActiveCell.Value = Application.UserName
                        End With
                        With ActiveSheet
                              LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                              Range("A" & LastRow).Select
                        End With
                    '7
                    End With
                '6
                End If

            '5
             End If
        '4
        End With

        '3
        End If

    '2
    End If


'1
End With


End Sub