Copy and Paste causing Run-Time Error '13'

cornks

New Member
Joined
Mar 2, 2011
Messages
4
Hi,

First time posting to the board. Apologies if I make any errors or break the forum etiquette.

I have a problem with some code that I have written and I am fairly new to VBA. I wrote some code so that if the cell A5 had a Yes or a No in it, then some columns would hide/unhide. (Cell A5 is a validated list) The code itself works fine. The issue is that every time that I copy and upon pasting in that sheet in another cell (say cell G16) I get:

Run-Time Error '13'
Type mismatch

Can somebody point me in the right direction? My current code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$E$5" And Target = "Yes" Then
Columns("G:I").EntireColumn.Hidden = False
Columns("AI:DF").EntireColumn.Hidden = False
Columns("EG:HE").EntireColumn.Hidden = False
ElseIf Target.Address = "$E$5" And Target = "No" Then
Columns("G:I").EntireColumn.Hidden = True
Columns("AI:DF").EntireColumn.Hidden = True
Columns("EG:HE").EntireColumn.Hidden = True
End If

Application.EnableEvents = True
End Sub


Thanks Heaps
cornks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
hi and welcome to the board.

can i suggest you step through your macro by placing a stop here:

Application.EnableEvents = False


and hitting F8 to see where the error is being generated. then get back to us
 
Upvote 0
Thanks for repsonding. Forgive me, today is my first day at trying to learn VBA. (I've been putting it off for years, but I couldn't any longer). I'm not sure what you mean by:

"by placing a stop here"

What I can tell you is if I hit debug. The following bit of code is highlighted in yellow:

If Target.Address = "$E$5" And Target = "Yes" Then

At this point I'm lost.

Thanks again.

Kind Regards,
cornks
 
Upvote 0
see if this fixes it

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Target.Address = "$E$5" And Target.value = "Yes" Then
        Columns("G:I").EntireColumn.Hidden = False
        Columns("AI:DF").EntireColumn.Hidden = False
        Columns("EG:HE").EntireColumn.Hidden = False
    Elseif Target.Address = "$E$5" And Target.value = "No" Then
        Columns("G:I").EntireColumn.Hidden = True
        Columns("AI:DF").EntireColumn.Hidden = True
        Columns("EG:HE").EntireColumn.Hidden = True
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
or maybe this...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$E$5" then exit sub
    Application.EnableEvents = False

     if Target.value = "Yes" Then
        Columns("G:I").EntireColumn.Hidden = False
        Columns("AI:DF").EntireColumn.Hidden = False
        Columns("EG:HE").EntireColumn.Hidden = False
    Elseif Target.value = "No" Then
        Columns("G:I").EntireColumn.Hidden = True
        Columns("AI:DF").EntireColumn.Hidden = True
        Columns("EG:HE").EntireColumn.Hidden = True
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
Thank you so much. That has worked a treat!!!:)

Just so I understand and learn from the experience, by placing the <> in the statement, and then the "then exit sub". You have told excel to only concentrate on cell A5 otherwise the macro is not relevant.

Thanks again.
cornks
 
Upvote 0
If Target.Address <> "$E$5" then exit sub

if the target address (the cell you pasted into) IS NOT equal to "E5" then exit the subroutine and do nothing
 
Upvote 0
just so I understand this you can not say
target address =

you have to say target address <>
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top