Copy one column to another

[Livin404]

Greetings, I have the following VBA that copies from B to O. It works great, but I would like it to work automatically as soon as data is place in Column B. I don't need a button for it. Also should I put it in a Module or on the worksheet "Sheet1" (Xman)"? Thank you,

Sub Copy()
Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Destination:=Range("O3")
End Sub

 

[JLGWhiz]

copy to worksheet code module for sheet where copy is needed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy Range("Q3")
    End If
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Livin404]

copy to worksheet code module for sheet where copy is needed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy Range("Q3")
    End If
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Thank you, that is very good. I do have Conditional Formatting in both Columns C and O. It looks like its copy the Conditional Formatting over too. The code I have for Column C is:

=OR(AND(B3="D",OR(LEFT(C3)<>"1",ISERROR(-C3),LEN(C3)<>10)),AND(B3="P",OR(LEN(C3)=0,LEN(C3)>12)))

The Conditional Formatting I have for Column O is:

=OR(AND(N3="M",OR(LEFT(O3)<>"1",ISERROR(-O3),LEN(O3)<>10)),AND(N3="P",OR(LEN(O3)=0,LEN(O3)>12)))

The end result the I have a "D" where a "M" should be. Is there anywhere I can copy and paste VBA without the Conditional Format? I do apologize I didn't realize it would also copy the formatting.

 

[JLGWhiz]

Replace the first code with this one. It will not carry the conditional formatting over to the new column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy 
        Range("O3").PasteSpecial xlPasteValuesAndNumberFormats
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Livin404]

Replace the first code with this one. It will not carry the conditional formatting over to the new column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy
        Range("O3").PasteSpecial xlPasteValuesAndNumberFormats
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

That works great, I do notice it leaves the green hatch boarder around the column to be copied and it is shaded where the items were pasted. I can select Escape and clear that. Is there one little line I can include like a select? Thank you, It safeguarded my formatting which was my biggest concern.

 

[JLGWhiz]

Add the line in red font.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy 
        Range("O3").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Livin404]

Add the line in red font.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.DisplayAlerts = False
    If Not Intersect(Columns(2), Target) Is Nothing Then
        Range("B3", ActiveSheet.Cells(Rows.Count, 2).End(xlup)).Copy
        Range("O3").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

I don't think I have anymore questions. Thank you for your unwavering support. You're great!

 

[JLGWhiz]

I don't think I have anymore questions. Thank you for your unwavering support. You're great!

You're welcome,
regards, JLG