Forum Discussion
when cell value changes, copy the information to a new worksheet but also retain each change
Try this.
But after entering the value press enter, don't try downarrow / tab .
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
ActiveCell.Offset(-1, 0).Activate
Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
End Sub
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
a = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("A" & a) = Sheets("Sheet1").Range("B2").Formula
End If
End Sub
Hello Sir,
I'm using your code for copying an updated cell from one sheet to another sheet however, I would like to copy the entire row of the updated cell into another sheet. Please advise as to what modifications I need to copy and paste the entire row. The code right now only copies the updated cell.
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
ActiveCell.Activate
a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Cast Worked").Range("A" & a).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End If
I've solved my problem.
This is the solution that works for me currently:
Sub CopyPaste()
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
myTarget.Activate
a = Sheets("Cast Worked").Cells(Rows.Count, "B").End(xlUp).Row + 1
myTarget.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
myTarget.Offset(1, 0).SelectEnd If
End Sub
It works for me, the only tuning I would like to change about it is for it to just copy values without any of the conditional formatting.
I tried this:
Sub CutPaste(myTarget As Range)
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
ActiveCell.Offset(1, 0).Select
End If
End SubBut this paste only to a single row each time.
- Just tell me the sheet names of your worksheet. And what things you want to do?
So I have a "Cast Sheet" which is where I work off of constantly, and when column F status changes, I want the entire row copied and pasted to "Cast Worked" to act as a data base so I can build reports and charts from it.
Currently this works:
Sub CopyPaste()
If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
myTarget.Activate
a = Sheets("Cast Worked").Cells(Rows.Count, "B").End(xlUp).Row + 1
myTarget.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
myTarget.Offset(1, 0).SelectEnd If
End Sub
This could be refactored because it shouldn't carry over any formatting. Also I have a macro in the quick access tool bar that pushes new data into "Cast Sheet" to be worked, and when the macro pushes the new data, that data is also copied and pasted to "Cast Worked."
Please advise as to how I can refactor my code.
