Worksheet change event help

[austenr]

I have a worksheet that when rows are added to it I want them to be copied to another worksheet. So, if sheet("Static") has rows added to the bottom they need to be copied automatically to the next available row in sheet("Summary")

Code for worksheet change event in the Static sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
UpdateFromStatic
End Sub

Sub in module:

Sub UpdateFromStatic()
    
    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    Range("A" & i).Value.Rows(i).Copy Destination:=Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    Next i


End Sub

Currently it doesn't copy anything.

[Paul_Hossler]

I don't think you were taking advantage of 'Target' range in the WS event


Not perfect since it assumes that Col A contains data to determine the last used row, and that you can't add (say) 10 rows which replace the last 5 and add 5 more



In a standard module

Option Explicit
 
Public iLastRowInStatic As Long
Public wsStatic As Worksheet, wsSummary As Worksheet

In ThisWorkbook

Option Explicit
Private Sub Workbook_Open()
    Set wsStatic = Worksheets("Static")
    Set wsSummary = Worksheets("Summary")
    
    With wsStatic
        iLastRowInStatic = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Sub

In WS "Static" module

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Rows(1).Row > iLastRowInStatic Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Call Target.EntireRow.Copy(wsSummary.Cells(wsSummary.Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow)
       iLastRowInStatic = wsStatic.Cells(wsStatic.Rows.Count, 1).End(xlUp).Row
       
        Application.EnableEvents = True
        Application.ScreenUpdating = True
       
    End If
End Sub

[austenr]

I downloaded your workbook and tried to enter data on the Static sheet. Doesn't auto copy to the Summary sheet.

[Paul_Hossler]

I think what was happening is that the iLastRowInStatic counter is set when you open the WB, but if you delete rows it doesn't get updated

Add this to the Workbook Module or try the attachment and see

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    With wsStatic
        iLastRowInStatic = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With wsStatic
        iLastRowInStatic = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Sub

[austenr]

That works fine. Thanks.

[austenr]

Spoke too soon. When trying to add a record to the Static Sheet, I get the following error:

object variable or with block variable not set.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With wsStatic
        iLastRowInStatic = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
End Sub

specifically on this line:

iLastRowInStatic = .Cells(.Rows.Count, 1).End(xlUp).Row

BTW, it compiles fine.

[Paul_Hossler]

wsStatic is Set in the WB open event, but some how was 'disconnected' and is now Nothing

Replace it with Worksheets("Static") as see

[austenr]

That fixed that. Now how can you copy just cells A:E from that row of Static to Summary?

[Paul_Hossler]

Probably something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rColumnsAtoE As Range
    If Target.Rows(1).Row > iLastRowInStatic Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Set rColumnsAtoE = Intersect(Target, Me.Columns("A:E"))
        
        Call rColumnsAtoE.Copy(Worksheets("Summary").Cells(Worksheets("Summary").Rows.Count, 1).End(xlUp).Offset(1, 0))
       iLastRowInStatic = Worksheets("Static").Cells(Worksheets("Static").Rows.Count, 1).End(xlUp).Row
       
        Application.EnableEvents = True
        Application.ScreenUpdating = True
       
    End If
End Sub

[austenr]

That works almost. Say to are pasting the cells in the Static sheet on row 59. It copies the cells on the summary to row 61 so I have 2 blank rows.

[austenr]

never mind user error. Thanks.