CODEWITHSUNDEEP
excelvba
David T. Shin
David T. Shin

Reputation: 19

Weird Issue of Argument Not Optional for Macro

I'm having trouble trying to get this code to work. It does work as intended without the (ByValue Target as Range) portion along with the ActiveCell.Value, but with these included in, I'm getting the error listed.

I have a number of other functions depending on this and would like to see what I could fix.

Thanks a lot!

Private Sub CT(ByVal Target As Range)
'
' CT Macro
'

    Sheets("Outbound Tactics").Select
    If ActiveCell.Value = "Yes" Then
        ActiveCell.Select
        Range(Selection, ActiveCell.Offset(0, 23)).Select
        Selection.Copy
        Sheets("Completed Tactics").Select
        ActiveSheet.Range("C4").Select
        Selection.End(xlDown).Offset(1, 0).Select
        ActiveSheet.Paste
        ActiveCell.Offset(1, 0).Select
        Application.CutCopyMode = False
        Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Outbound Tactics").Select
        ActiveCell.Select
        Range(Selection, ActiveCell.Offset(0, 23)).Select
        Selection.Delete
    End If
End Sub

PS: From what I understand, the beginning portion is there in order to have the macro run continuously without being called in.

Upvotes: 1

Views: 135

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Seeing as you don't actually use the Target object in your code I'm assuming you copy/pasted this from somewhere and are not actually sure what it's there for.

Simple answer: remove it.

Private Sub CT()
'// your code here
End Sub

Extended Answer: incorporate it into your code.

Private Sub CT(ByVal Target As Range)

    If Target.Value = "Yes" Then
        With Sheets("Completed Tactics").Range("C4").End(xlDown).Offset(1, 0)
            .Resize(1, 24).Value = Target.Resize(1, 24).Value
            .Offset(1, 0).EntireRow.Insert CopyOrigin:=xlFormatFromLeftOrAbove
        End With

        Target.Resize(1, 24).Delete

    End If
End Sub

Upvotes: 2

Related Questions