Forum Discussion
Copy specific cell to new worksheet but keep original cell value
Thank you so much. I figured out what I was doing wrong so I have the original formula working.
Sh2.Range
I had to change that to: Sheets("the name of my sheet").Range
Now I'll look over what you just posted and get back to you.
I really appreciate the help.
Is it simply to make a change so that ANY value that is changed in column B and column E would do the same?
Do you mean that you want to get rid of using the button and make this code triggers in case if the cell B2 or E2 of the Sheet 1 is changed?
If so, please right-click the Sheet 1 tab and choose View Code, and then copy and paste this code there:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B2, E2")) Is Nothing Then
Dim r1, r2 As Integer
r1 = Application.WorksheetFunction.CountA(Sh2.Range("B:B"))
r2 = Application.WorksheetFunction.CountA(Sh2.Range("E:E"))
Sh2.Range("B1").Offset(r1).Value = Sh1.Range("B2").Value
Sh2.Range("E1").Offset(r2).Value = Sh1.Range("E2").Value
Sh2.Activate
Sh2.Range("E1").Offset(r2).Select
End If
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
well I did remove the button and got it to work automatically and it works great!
But how difficult would it be to make it so on any of the "B" columns it would do the same thing? Not just B2 and E2? So any time I change any value in columns "B" or "E" it would still work the same?
basically do it's code no matter what cell in column "B" get's changed.
hopr37 hotmail wrote:well I did remove the button and got it to work automatically and it works great!
But how difficult would it be to make it so on any of the "B" columns it would do the same thing? Not just B2 and E2? So any time I change any value in columns "B" or "E" it would still work the same?
basically do it's code no matter what cell in column "B" get's changed.In this case, please test this code:
Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B:B, E:E")) Is Nothing Then
Dim r1, r2 As Integer
r1 = Application.WorksheetFunction.CountA(Sh2.Range("B:B"))
r2 = Application.WorksheetFunction.CountA(Sh2.Range("E:E"))
Sh2.Range("B1").Offset(r1).Value = Sh1.Range("B" & Target.Row).Value
Sh2.Range("E1").Offset(r2).Value = Sh1.Range("E" & Target.Row).Value
Sh2.Activate
Sh2.Range("E1").Offset(r2).Select
End If
Application.ScreenUpdating = True
On Error GoTo 0
End Sub- sir above code work perfectly when cell was updated manually but if cell upadated due to formula result it is not working please sir help me if possible
