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.....

 

[Glory]

Private Sub Worksheet_Change()

If Intersect(Target,Sheets("Name").Range("A1:A65536")) Then

Target.Offset (0, 3).Value = Target

End If

End Sub

Edit: Put this into the worksheet module, not some random standard module.

 

[jeffreybrown]

If I understand you right, this should work

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim Rng As Range
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    Set Rng = Target.Parent.Range("A:A")
    '   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    '   Only look at that range
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    '   Action if Condition(s) are met (do your thing here...)
    Target.Offset(0, 3).Value = Target.Value
End Sub

 

[reylan]

No but you almost got it.. I only type in coloum A1.
When I type a first character in A1 it would copy to D1
and second character still type in A1 since D1 is filled up the second charater is copied to D2.
Coloumn A1 is there source and date is the list or history

 

[jeffreybrown]

Hi reylan,

Try this adjustment...

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim Rng As Range
    Dim LC As Long
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    Set Rng = Target.Parent.Range("A1")
    '   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    '   Only look at that range
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    '   Action if Condition(s) are met (do your thing here...)
    If Range("D1").Value = "" Then
        Target.Offset(0, 3).Value = Target.Value
    Else
        Target.Offset(0, LC).Value = Target.Value
    End If
End Sub

 

[reylan]

Almost there...there is a bit of adjustment instead of copying the words by row I want them in Coloumn D only. All characters type in A1 would be copied to Coloumn D only not to E, F, G, H, and so on...

 

[jeffreybrown]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim Rng As Range
    Dim LR As Long
    LR = Cells(Rows.Count, 4).End(xlUp).Row
    '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    Set Rng = Target.Parent.Range("A1")
    '   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    '   Only look at that range
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    '   Action if Condition(s) are met (do your thing here...)
    If Range("D1").Value = "" Then
        Target.Offset(0, 3).Value = Target.Value
    Else
        Target.Offset(LR, 3).Value = Target.Value
    End If
End Sub

 

[reylan]

thank you so much. if ever i wanted to change Coloumn a1 to another coloumn let us say to C1 and coloumn D to another coloumn.
what would i change with this code.

 

[jeffreybrown]

thank you so much. if ever i wanted to change Coloumn a1 to another coloumn let us say to C1 and coloumn D to another coloumn.
what would i change with this code.

The part's in red would require changed. If have commented the code so more.

Hope it helps.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Code goes in the Worksheet specific module
    Dim Rng As Range
    Dim LR As Long
    
    'Here the variable LR is set to column D
    'This is used latter in the If statement to capture the last used cell in column D
    LR = Range([COLOR="Red"]"D"[/COLOR] & Rows.Count).End(xlUp).Row
    
    'If you do not want to start with A1...set it here
    'Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    Set Rng = Target.Parent.Range([COLOR="red"]"A1"[/COLOR])
    
    If Target.Count > 1 Then Exit Sub 'Only look at single cell changes
    If Intersect(Target, Rng) Is Nothing Then Exit Sub 'Only look at that range
    
    'If you do not want the value from A1 stored in column D with D1 as the first cell...change it here
    'Action if Condition(s) are met (do your thing here...)
    If Range([COLOR="red"]"D1"[/COLOR]).Value = "" Then
    
        'The first time this macro is run the If statement will execute here
        'Here we offset from A1 by 3 columns which is D1
        Target.Offset(0, [COLOR="red"]3[/COLOR]).Value = Target.Value
    Else
    
        'After the first run through the macro will execute here
        'Here we offset from A1 which is D1 and the variable LR produces a 1
        'in the end...offset 3 columns to the right from A1 (D1) and then 1 row down (D2)
        'and so on
        Target.Offset(LR, [COLOR="red"]3[/COLOR]).Value = Target.Value
    End If
End Sub

 

[reylan]

very useful code can i add a coloumn that would do the same as A1 to D1 did.
When I type in B1 the character in B1 would go to E1.
Is this possible in one code?

Thanks