Error 1004: Select method of Class Failed

This seems to be a popular ‘Google’ find! I didn’t know what to do when I was confronted with this error! And Google was there to my rescue…

Most of you must have hit upon this error, and by far, I saw more posts that had this question without a solution! Let me muster a few thoughts that I was able to gather from various places on the Internet!

Working with Event Handlers, most of you must have tried a code like the one below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim copyrng As Range, pasterng As Range
    Sheets2.Select
    Set copyrng = Sheets2.Range(“A1”)
    Set pasterng = Target.Value
    copyrng.Copy
    pasterng.PasteSpecial xlPasteValuesAndNumberFormats
End Sub

What is amiss here is that this code is an Event Handler, written in Sheet1’s Worksheet_SelectionChange. And we’re selecting Sheet2 from here! Isn’t there a problem that you see???

Working with Event Handlers, it’s best not to pick ranges from other sheets inside them directly. If there is a requirement to do so, it’s best to use Modules.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Call Module1.Test
End Sub

Sub Test()
    Dim copyrng As Range, pasterng As Range
    Sheets2.Select
    Set copyrng = Sheets2.Range(“A1”)
    Set pasterng = Target.Value
    copyrng.Copy
    pasterng.PasteSpecial xlPasteValuesAndNumberFormats
End Sub

I had really started to even doubt my own knowledge of the Range functions. Anything with the range will start to work in a funny way, and you’ll be totally thrown off. Hope it helps more people who fall into the trap of “Select method of Class Failed”

Also, this is probably just one occurance that I encountered! I encourage more people to come up with their instances so we know when all this can actually happen..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s