1

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.

Community
  • 1
  • 1
David T. Shin
  • 19
  • 3
  • 2
    How are you calling the sub? When calling the sub you must pass a range.: `CT(Range("A1"))` or just remove it as you do not do anything with it. On another note one should avoid using select. See here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Scott Craner Dec 19 '16 at 21:21
  • It's not weird at all. If you tell the macro it needs a target to work it will require you to introduce it every time you call it. By looking at your code there is no point of having Target as input since it's not used ever. [More info here](http://www.functionx.com/vbaexcel/functions/Lesson3.htm) – Sgdva Dec 19 '16 at 21:22
  • 1
    The Worksheet_Change event cannot be renamed - I'm guessing that is what you have done? – SJR Dec 19 '16 at 21:26
  • Thanks Scott adn Sgdva. I'll take a look at that avoiding select. Thanks. – David T. Shin Dec 19 '16 at 21:27

1 Answers1

2

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
Graham
  • 7,431
  • 18
  • 59
  • 84
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • I'm not quite sure this may be a suitable answer to the OP, but I won't downvote because it's partial due to OP broad in their statement. – Sgdva Dec 19 '16 at 21:23
  • Apologies, but the reason why it's there is due to wanting this macro to run continuously without flipping back into it. – David T. Shin Dec 19 '16 at 21:24
  • 1
    @Sgdva The reason for the error is that `Target` isn't supplied when calling the sub - as it's not actually being used within the sub - there's no reason for it in the header. I'm not sure how that isn't a suitable answer? – SierraOscar Dec 19 '16 at 21:30
  • @DavidT.Shin You would need to change it as macro man does in the extended answer, as now, there is no where in your code you are using Target (hence, useless) – Sgdva Dec 19 '16 at 21:31
  • 1
    @DavidT.Shin Having it there has no effect on being able to call the macro, it's actually what is _stopping_ you from being able to call the macro. – SierraOscar Dec 19 '16 at 21:31
  • Hi Macro Man, I'm still hitting an error about this- same Argument Not Optional after using this line. (by the way, this code alone cleans up everything I was taught. Thank you!) – David T. Shin Dec 19 '16 at 22:01
  • If you're using the extended version then you need to call it by supplying the `Target` for example `CT ActiveCell` or `Call CT(ActiveCell)` The easiest option however is to leave your code as it is for now and simply remove the "`ByVal Target As Range`" <-- this will **not** stop the code from running. – SierraOscar Dec 19 '16 at 22:03
  • My issue with removing it is that it won't run the code constantly upon a change in the cell value. What would remedy that? – David T. Shin Dec 19 '16 at 22:11
  • If you want it to run automatically when a cell changes then you need to use a change event - which is completely different and you can't call the Sub whatever you want. – SierraOscar Dec 19 '16 at 22:23