Copy to other sheet

[reylan]

I typed a word in Coloumn a1 which this word will be filed or auto copy to Coloumn D1. every time I typed a word in Coloumn A1 it will be copy to coloumn D1 and next word to d2 and next to d3 and so on.....

 

[jeffreybrown]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim LR  As Long
        
    Set Rng = Target.Parent.Range("A1,B1")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    Select Case Target.Address
    
    Case Is = "$A$1"
    
        If Range("D1").Value = "" Then
            Target.Offset(0, 3).Value = Target.Value
        Else
            LR = Range("D" & Rows.Count).End(xlUp).Row
            Target.Offset(LR, 3).Value = Target.Value
        End If
    
    Case Is = "$B$1"
    
        If Range("E1").Value = "" Then
            Target.Offset(0, 3).Value = Target.Value
        Else
            LR = Range("E" & Rows.Count).End(xlUp).Row
            Target.Offset(LR, 3).Value = Target.Value
        End If
    
    End Select
End Sub

 

[reylan]

if i extend to C1 and so on what would i do. I tried to put a password to my sheet1 so that the user would not mess around and delete the history in D1 and E1 but suddenly the code doesnt work. Please help.

 

[jeffreybrown]

From what I understand, or order for macros to run on a protected worksheet, you will need to use the userinterfaceonly:=True in the workbook protect method. This is one of the many arguments.

I also believe this only works in the Workbook Open event which is the macro below.

Check out the Excel help on the Worksheet.Protect Method

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Protect Password:="Password", userinterfaceonly:=True
    Next ws
End Sub

Another method is to unprotect/protect inside the worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Unprotect Password:="Password"
    'Run code here
    Protect Password:="Password"
End Sub

Here is the Unprotect procedure

'Standard Module

Sub WSunprotect()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Unprotect Password:="Password"
    Next ws
End Sub