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.