Activate as soon as pasted

[Livin404]

Greetings, I have this basic code. I would like it to be activated as soon as I paste my data to my worksheet. I know it works, jus want the macro to activate as soon as I paste it. I would like to avoid having buttons. The code is

Sub delCol()
        Dim sourceSheet As Worksheet
        Set sourceSheet = Sheet1
     sourceSheet.Range("A:A, G:H, J:L, N:X, Z:AD").EntireColumn.Delete
    End Sub

The following code is what I have in one of my modules. If i am going to post as a "private" Sub I already have

Private Sub Worksheet_Change(ByVal Target As Range)
Columns.AutoFit
End Sub

Thank you!

 

[Joe4]

Just call it from your "Worksheet_Change" event procedure, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call delCol
    Columns.AutoFit
End Sub

 

[Livin404]

Just call it from your "Worksheet_Change" event procedure, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call delCol
    Columns.AutoFit
End Sub

Thanks for the prompt response, I get a error box up stating "Out of Stack Space". I put exactly what you typed in Sheet 1 while I left my original code to remove the columns in the module. Not sure what is happening.

 

[Joe4]

It is probably invoking itself and getting caught in a loop. Try this updated version:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call delCol
    Application.EnableEvents = True
    Columns.AutoFit
End Sub

 

[Livin404]

It is probably invoking itself and getting caught in a loop. Try this updated version:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Call delCol
    Application.EnableEvents = True
    Columns.AutoFit
End Sub

Beautiful! Thank you! exactly what I wanted.

 

[Joe4]

You are welcome.
Glad I was able to help!