Hi, If we can keep data to stay at the old position as well. For eg: If we toggle between date?
I'm guessing you are asking for the data to be stacked on top on each other if data is already there? If so...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5")) Is Nothing Then
Dim wsSrc As Worksheet: Set wsSrc = Sheets("Before")
Dim wsDest As Worksheet: Set wsDest = Sheets("After")
Dim lrDest As Long
Application.ScreenUpdating = False
If Target.Value = "" Then Exit Sub
With wsDest
lrDest = .Cells(Rows.Count, Day(Target)).End(xlUp).Row
If lrDest = 1 Then
lrDest = lrDest
Else
lrDest = lrDest + 1
End If
End With
wsSrc.Cells(Target.Offset(2).Row, 3).Resize(4).Copy wsDest.Cells(lrDest, Day(Target))
Application.ScreenUpdating = True
Application.Goto wsDest.Range("A1")
Set wsSrc = Nothing
Set wsDest = Nothing
End If
End Sub
How can I get this functionality if I only have one sheet?
I'm not so clear on this. Do you mean you just have one sheet so you want the results to be pasted above the drop down which is in row 5?
Bookmarks