0

I have a macro to add the time when a cell is change in Sheet1(macro 1), but it doesn't update automatically. If I enter a value manualy, the macro works. I have another macro to copy form sheet2 to sheet1(macro2) the macro1 doesn't work. Someone can help me to understand the issue?

Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range

    Set rInt = Intersect(Target, Range("tbl_summary[Request ID]"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            Set tCell = rCell.Offset(0, -2)
            If IsEmpty(tCell) Then
                tCell = Now
                tCell.NumberFormat = "mmm d, yy   hh:mm"
            End If
        Next
    End If
End Sub

here is the code to copy and paste the values

Public Sub Move_AssignedWork()
    Dim lRowF As Long

    ThisWorkbook.Activate
    lRowF = ShWorkSummary.Cells(ShWorkSummary.Rows.Count, 6).End(xlUp).Row
    lRowF = lRowF + 1
    
    Worksheets("MoveItems").Select
    With ActiveSheet.ListObjects("Tbl_template")
        .Range.AutoFilter Field:=20, Criteria1:="Sent"
        Union(.ListColumns(1).DataBodyRange, _
            .ListColumns(2).DataBodyRange, _
            .ListColumns(3).DataBodyRange, _
            .ListColumns(4).DataBodyRange, _
            .ListColumns(12).DataBodyRange, _
            .ListColumns(13).DataBodyRange, _
            .ListColumns(14).DataBodyRange, _
            .ListColumns(15).DataBodyRange, _
            .ListColumns(16).DataBodyRange, _
            .ListColumns(17).DataBodyRange, _
            .ListColumns(18).DataBodyRange, _
            .ListColumns(19).DataBodyRange).Copy
    End With

    ShWorkSummary.Select
    Range("D" & lRowF).PasteSpecial Paste:=xlPasteValues
      
    Worksheets("MoveItems").Select
    With ActiveSheet.ListObjects("Tbl_template")
        .Range.AutoFilter Field:=20
    End With
    
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
mcmao
  • 1
  • 1
  • Please also include the code of the procedure that copies the values. We need a [mcve] to understand what goes wrong. – Pᴇʜ Apr 28 '21 at 09:56
  • What is the name of the worksheet that contains the `Worksheet_Change` event? Is it `ShWorkSummary`? And is `Range("D" & lRowF)` whithin `tbl_summary[Request ID]` or below? What is the result of `Debug.Print Range("D" & lRowF).Address` and `Debug.Print Range("tbl_summary[Request ID]").Address`? • I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this to your code, using `.Select` and `ActiveSheet` is a bad practise and makes your code unreliable. – Pᴇʜ Apr 28 '21 at 11:22
  • Hello I have added the code – mcmao Apr 28 '21 at 13:09
  • Worsheet nameShWorkSummary, thanks for teh recommendation I will read it. The code was working well, but suddenly it stopped working. – mcmao Apr 28 '21 at 13:21
  • *"suddenly it stopped working"* is a typical issue when using `.Select`. Please give us the result of `Debug.Print Range("D" & lRowF).Address` right before you do `PasteSpecial` and the result of `Debug.Print Range("tbl_summary[Request ID]").Address` in the worksheet change event. – Pᴇʜ Apr 28 '21 at 15:17

0 Answers0