Need help enter some new code

Question

I am needing help as every time i try to add more code it does not work
what i am needing to add

if colC is edited then copy value in colF and paste in "sheet3 Cell A2"

colF does have a formula and i am only needing the value from that formula to be copy to the other sheet

if C1 is edited then copy F1 and paste in "sheet3 Cell A2"

if C2 is edited then copy F2 and paste in "sheet3 Cell A2"
if c3 is edited then copy f3 and paste in "sheet3 Cell A2"

------------------=Current code for sheet=-------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim mytable As Range  
  Set mytable = Range("A2", "A50")  
  
Dim Tr As Range  
  Set Tr = Application.Intersect(Target, mytable)  
    If Tr Is Nothing Then Exit Sub  
   
Application.EnableEvents = False  
  Me.Unprotect Password:="Password"  
  
If Tr.Value = "" Then  
  Tr.Offset(0, 3).ClearContents  
ElseIf IsNumeric(Tr.Value) Then  
  Tr.Offset(0, 3).Value = Now()  
     Target.Formula = "=VLOOKUP(" & Target.Value & ",login!A1:B70,2,FALSE)"  
  
End If  
    Me.Protect Password:="Password"  
      Application.EnableEvents = True  

End Sub

Answer 1

also just remember that i need to also do

With Target(1).Offset(0, -2)
Sheet3.Range("a1").NumberFormat = .NumberFormat
Sheet3.Range("a1").Value2 = .Value2

Use VBA help to learn about the structure of the With statement.

See the complete VBA procedure below. Alternatively, download "new+old Wrksht-Chng.xlsm" (click here). Ignore any preview errors and request to login. Just proceed to download the file.

BTW, I don't see how you expect anyone to edit C1:C2 -- or A2:A50, your original range -- when the worksheet is protected by a password. No need to explain to me. Just bringing it to your attention.

In the code below, pay close attention to other changes, highlighted in red.

---

Private Sub Worksheet_Change(ByVal Target As Range)

Const mypass As String = "password" '*** new ***
' *** new code
If Not Intersect(Target, Range("c1:c3")) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:=mypass
With Target(1).Offset(0, -2)
Sheet3.Range("a1").NumberFormat = .NumberFormat
Sheet3.Range("a1").Value2 = .Value2
End With
With Target(1).Offset(0, 3)
Sheet3.Range("a2").NumberFormat = .NumberFormat
Sheet3.Range("a2").Value2 = .Value2
End With
Me.Protect Password:=mypass
Application.EnableEvents = True
Exit Sub
End If

' *** old code
Dim mytable As Range
Set mytable = Range("A2", "A50")

Dim Tr As Range
Set Tr = Application.Intersect(Target, mytable)
If Tr Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Unprotect Password:=mypass '*** modified ***

If Tr.Value = "" Then
Tr.Offset(0, 3).ClearContents
ElseIf IsNumeric(Tr.Value) Then
Tr.Offset(0, 3).Value = Now()
Target.Formula = "=VLOOKUP(" & Target.Value & ",login!A1:B70,2,FALSE)"
End If

Me.Protect Password:=mypass '*** modified ***
Application.EnableEvents = True
End Sub

Answer 2

OK i see i got it to work Thanks!!
also just remember that i need to also do

With Target(1).Offset(0, -2)
Sheet3.Range("a1").NumberFormat = .NumberFormat
Sheet3.Range("a1").Value2 = .Value2

how would that go in i tried

Me.Unprotect Password:=mypass
With Target(1).Offset(0, 3)
Sheet3.Range("a2").NumberFormat = .NumberFormat
Sheet3.Range("a2").Value2 = .Value2

then

With Target(1).Offset(0, -2)
Sheet3.Range("a1").NumberFormat = .NumberFormat
Sheet3.Range("a1").Value2 = .Value2

End With

but it did not like that

Answer 3

i still need the old code with the new code

That's what I intended when I wrote:

'*** old code

Dim mytable As Range

Set mytable = Range("A2", "A50")

[... etc ...]

Answer 4

i still need the old code with the new code

Answer 5

Errata: I overlooked this comment: colF does have a formula and i am only needing the value from that formula to be copy to the other sheet

Modified code:

Private Sub Worksheet_Change(ByVal Target As Range)

Const mypass As String = "password"

'*** new code

If Not Intersect(Target, Range("c1:c3")) Is Nothing Then
Application.EnableEvents = False

Me.Unprotect Password:=mypass  
With Target(1).Offset(0, 3)  
    Sheet3.Range("a2").NumberFormat = .NumberFormat  
    Sheet3.Range("a2").Value2 = .Value2  
End With

Me.Protect Password:=mypass  
Application.EnableEvents = True  
Exit Sub  

End If

'*** old code

Dim mytable As Range

Set mytable = Range("A2", "A50")

[... etc ...]

End Sub

That adds new code to old code, if you wish.

I use Target(1).Offset(0,3) instead of Target.Offset(0,3) just in case Target is multiple cells. That might be the case, for example, when we use ctrl+Enter to enter data or a formula into multiple cells all at once.

Since you paste to the same cell (Sheet3!A2), it is arbitrary which of the multiple Target cells that we choose to copy-and-paste. Target(1) always works, even when Target is a single cell.