Macro help

[zcemf64]

I'm trying to get excel to copy the B,C,D and E column of the same row and paste onto cells L2, M2, N2 and O2 when a cell in B is selected. Currently I'm using this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 2 Then

Target.Offset(0, 0).Copy Destination:=Range("L2")
Target.Offset(0, 1).Copy Destination:=Range("M2")
Target.Offset(0, 2).Copy Destination:=Range("N2")
Target.Offset(0, 3).Copy Destination:=Range("O2")

End If

End Sub

However, I can't run this as a macro and I have to copy and paste this onto the sheet in vba. Is there a way I could rewrite this so it is part of a macro? Would really appreciate help. Thanks.

 

[AlphaFrog]

Is this what you want?

Sub Macro1()

    If ActiveCell.Column = 2 Then
        ActiveCell.Resize(, 4).Copy Destination:=Range("L2")
    End If

End Sub

 

[zcemf64]

is there a way for excel to copy and paste immediately as i select different cells? because with your way, i have to run the macro each time after i select a cell. thanks for helping out.

 

[AlphaFrog]

Isn't that what your original macro does? I don't understand what you want.

 

[zcemf64]

sorry i'm not being clear. my original macro allows me to copy and paste immediately/live without having to run the macro each time i select a new cell.

the macro you suggested requires me to run the macro again when i select a new cell.

hope that makes sense.

 

[AlphaFrog]

sorry i'm not being clear. my original macro allows me to copy and paste immediately/live without having to run the macro each time i select a new cell.

the macro you suggested requires me to run the macro again when i select a new cell.

hope that makes sense.

I understand what the original macro does and what the macro I proposed does. The part I do not understand is what you want to do that either one of those macros don't do.

I've read this several times.

However, I can't run this as a macro and I have to copy and paste this onto the sheet in vba. Is there a way I could rewrite this so it is part of a macro?

This is what I don't understand. Could you better explain?

 

[zcemf64]

oh ok. basically, i want to run my original macro on a new worksheet in excel. however, i can't just click run macro for the macro to work. it doesnt appear on the list of macros when i want to run it. i have to copy the macro from a module onto the sheet 2 in microsoft visual basic under microsoft excel objects. you know, when you press alt+11.

on the other hand, your macro appears of the list of macros to run. it's just that i would prefer it to copy and paste immediately as i click on different cells.

thanks for bearing with me dude. i hope this is a slightly better explanation.

 

[AlphaFrog]

Put this code in the Thisworkbook module. It will work for all worksheets in the workbook. Even for the new ones you add.

It includes a line (red) to exclude specific sheets (Sheet1 and Sheet2 in this example) if you want it not to work on those.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    'This works for all sheets in the workbook
    
    'This line will exclude specific sheets by name
    [COLOR="Red"]If Sh.Name <> "Sheet1" And Sh.Name <> "Sheet2" Then[/COLOR]
        'Copy\paste cell B
        If Target.Column = 2 Then
            Target.Resize(, 4).Copy Destination:=Sh.Range("L2")
        End If
    [COLOR="Red"]End If[/COLOR]
    
End Sub

 

[zcemf64]

it works perfectly! thanks