Run macro on event

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
117
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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?

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

End Sub
 

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81
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:
VBA Code:
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
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,648
Members
410,696
Latest member
JTrehan
Top