Convert event double click code to simple code

[MOB]

A couple of years ago I constructed this code with the help of Mrexcel;

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim WasProtected As Boolean
    Dim response As VbMsgBoxResult
    Dim adjust As Integer
    
    On Error GoTo myhandler
    
    WasProtected = CBool(Me.ProtectContents)
    
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    If Target.Row = 4 Then
        
        If Target.Column > 11 And Target.Column < 42 Then
            
            response = MsgBox("This will copy yesterday's data over to today - confirm ok?", vbYesNo, "Copy Yesterday's Data")
            adjust = -1
        ElseIf Target.Column = 11 Then
            
            response = MsgBox("This will copy the last day's data over to the first day, AND CLEAR DAYS 2 ONWARDS - confirm ok?", vbYesNo, "MONTH END CARRY FORWARD")
            adjust = (Range("D3") - 1)
        End If
        
    End If
    
    If response = vbYes Then
        Application.ScreenUpdating = False
        If WasProtected Then Me.Unprotect Password:="***"
        Target.Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn
        
    If Target.Column = 11 Then
        Range("L6:AO" & lastrow).ClearContents
    End If
           
        Me.Range("A7").Select
        If WasProtected Then Me.protect Password:="***"
    End If
    
myhandler:
    Application.ScreenUpdating = True
End Sub

Double clicking on row 4 between columns K and AO triggered a copy/paste/clear routine. Works great.

However I now want to extract part of this to use in another bit of code, not triggered by the double click.

I am only interested in the bit that refers to target column 11 - not the >11 and <42 option

I'm also not interested in the message boxes, I just want to extract the basic code to copy/paste/clear.

If it helps D3 contains the number of days in a month, columns 11-42 days of the month 1-31

TIA

I've tried various versions and just cant get it to work!!

 

[dmt32]

Hi,
This is just a guess but see if below goes in right direction

I have taken code I think you want & created separate procedure

 Sub CopyColumn11(ByVal Target As Range)    
     Dim adjust As Integer
    Dim LastRow As Long
    
    If Not Target.Column = 11 Then Exit Sub
    
    adjust = Target.Parent.Range("D3").Value - 1
    Target.Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn
        
    With Target.Parent
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("L6:AO" & LastRow).ClearContents
    End With
    
End Sub

Call it by passing the range to it

 CopyColumn11 Range("K20")

Suggestion untested & you should adjust as required to meet specific project need.
If suggestion does not meet your requirement then post your attempts so board can get better understanding of what trying to do – plenty here to offer assistance.

Dave

 

[MOB]

Many thanks, with your suggestion and a little trial and error this seems to have done the trick;

Dim adjust As Integer
     
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
adjust = (Range("D3") - 1)
            
Range("K4").Select
Range("K4").Offset(, adjust).EntireColumn.Copy Destination:=ActiveCell.EntireColumn

Range("L6:AO" & lastrow).ClearContents