Confused

[topi1]

I am really confused.

I am running the following macro.

Sub StartNew()
'
' StartNew Macro
'
'
    Range("E29,E30,J30,J29,L29,L30").Select
    Range("L30").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B28").Select
    Selection.Copy
    Range("B28,N25,N26,N27,N28").Select
    Range("N28").Activate
    ActiveSheet.Paste
    Range("B72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B72,E25,J25,L25").Select
    Range("L25").Activate
    ActiveSheet.Paste
   Range("B30:B33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D19").Select
    ActiveSheet.Paste
    Range("B35:B47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D3:D15").Select
    Range("D15").Activate
    ActiveSheet.Paste
    Range("B9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B9,O16,O20").Select
    Range("O20").Activate
    ActiveSheet.Paste
    Range("O4:O15").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("O4").Select
End Sub

However, I get the error message #13 and says type mismatch. When I hit debug, the following red line is highlighted from a separate macro which I have in the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[COLOR=#FF0000] If Target.Value = "No" Then[/COLOR]
 Target = "Yes"
 ElseIf Target.Value = "Yes" Then
 Target = "No"
 End If
 End Sub

Any solution?
Thanks.

 

[TMShucks]

I would guess it's because the Selection Change event handler fires when you select six non-contiguous cells. It then tries to compare the Target (six cells) to the single value "No".

You should avoid all the Selecting you do; it's not necessary to select something to copy or manipulate it.

 

[topi1]

Thanks for your reply. I am not sure how to reset the fields (some of which are data validation drop down containing cells) without selecting, copying and pasting.

 

[Michael M]

What TMS is suggesting is for example, your first 6 lines of code can be reduced to

Range("E29,E30,J30,J29,L29,L30").Select
Range("L30").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("B28").Select
Selection.Copy

TO

Range("E29,E30,J30,J29,L29,L30").ClearContents
Range("B28").Copy

 

[TMShucks]

Range("E29,E30,J30,J29,L29,L30").ClearContents
Range("B28").Copy Range("B28,N25,N26,N27,N28")
Range("B72").Copy Range("B72,E25,J25,L25")
' etc

(first 15 lines)

 

[Michael M]

@TMS....I didn't know we were going to do the whole thing...

 

[TMShucks]

@Michael: I didn't intend to ... just got on a roll . I wanted to give a couple of examples. There's still the second half for the OP to have a go at.

 

[topi1]

Thank you both much. Greatly appreciated. I am okay with excel but do not know VBA. As you must have figured out, I just recorded my actions and created the macro.I will try your suggestions. Thanks again.