Method Of Range Class Failed (SOLVED) - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Method Of Range Class Failed (SOLVED)

  1. #11
    Board Regular
    Join Date
    Mar 2011
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

     
    Quote Originally Posted by Firefly2012 View Post
    Hi
    Avoid using Select at all:

    Code:
    Sub Backup()
    Dim xx As Integer
    xx = Sheets("Data").Range("C1").Value
    With Sheets("Data").Range("B3:P4").Offset(xx, 0).Interior
            
      .Pattern = xlSolid
            
      .PatternColorIndex = xlAutomatic
            
      .ThemeColor = xlThemeColorDark1
            
      .TintAndShade = -0.25
            
      .PatternTintAndShade = 0
        
    End With
    End Sub
    Would avoiding Select solve this issue? I'm having the same problem but don't know how I could go about avoiding Select.
    My code:
    Code:
    Sub CopyAppend()
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Sheets("K2").Select
        Sheets("K2").Range("$B$16:$J$23").Select
            Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
            
        Sheets("Append").Select
        Range("A1").Select
            Lastrow = [a65000].End(xlUp).Row 'count the last row number
            Cells(Lastrow, 1).Offset(8, 0).Select 'select cell column A and offset last row
                ActiveCell.Value = "."
                ActiveCell.Offset(-8, 0).Select
                    Worksheets("Append").Pictures.Paste
            
        Sheets("Impressão das Etiquetas").Select
        Range("C5").Select
        
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        
    End Sub

  2. #12
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,766
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Try something like this:
    Code:
    Sub CopyAppend()
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Sheets("K2").Range("$B$16:$J$23").CopyPicture Appearance:=xlScreen, Format:=xlPicture
            
        With Sheets("Append")
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'count the last row number
            With .Cells(Lastrow, 1)
                .Offset(8, 0).Value = "."
                .PasteSpecial xlPasteAll
            End With
        End With
                
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        
    End Sub

  3. #13
    Board Regular
    Join Date
    Mar 2011
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Quote Originally Posted by RoryA View Post
    Try something like this:
    Code:
    Sub CopyAppend()
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Sheets("K2").Range("$B$16:$J$23").CopyPicture Appearance:=xlScreen, Format:=xlPicture
            
        With Sheets("Append")
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'count the last row number
            With .Cells(Lastrow, 1)
                .Offset(8, 0).Value = "."
                .PasteSpecial xlPasteAll
            End With
        End With
                
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        
    End Sub
    .PasteSpecial xlPasteAll causes error. Tells me: RT error 1004 "PasteSpecial method of Range class failed"

  4. #14
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,766
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Oops - remove the xlPasteAll

  5. #15
    New Member
    Join Date
    Jun 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    I am looking for a formula that searches different columns and depending on which column it matches it returns the name of the top of the column it is found in.
    Example: If a specific zip code (in the labeled zip code column) matches any zip code under the contractor column (column labeled by contactor name) it should pull the contractor name and insert it into the Responsible Party Column.
    Sorry if I'm not explaining this well. Please HELP!

  6. #16
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Quote Originally Posted by Elemental View Post


    i just figured out that when you change sheets too often in VBA and get an "Activate Method Of Range Class Failed" or a "Select Method Of Range Class Failed" etc, simply retype the sheets command before it

    for example :

    Sheets("Work1").Select
    Range("A1:D50").Select

    OR

    Sheets(Work1").Range("A1:D50").Select

    this code in VBA, if used too often or the mentioned sheet is not the currently selected sheet in a Sub, will cause errors, and to correct this, simply type

    Sheets("Work1").Select
    Sheets("Work1").Range("A1:D50").Select

    this makes sure that the sheet is selected before running any other range/sheet type commands.

    I hope this clears up some other ppl's problems, it had me for a few weeks.
    Hi guys, I just wanted to write that I use ".Select" in all the macros I'm creating.. and the trick in avoiding that "Select Method Of Range Class Failed" message is pretty simple. Just make sure that in Excel, the selected Sheet is the same one you are creating your Macro for. For example Sheet1 in Excel --> Sheet1 in VBA.

    Thats all.

  7. #17
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,845
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Welcome to the board.

    Hi guys, I just wanted to write that I use ".Select" in all the macros I'm creating.. and the trick in avoiding that "Select Method Of Range Class Failed" message is pretty simple. ...
    Yes, it is, and has been suggested several times in this thread: Don't use Select. It's unnecessary and slows your code.

  8. #18
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Quote Originally Posted by shg View Post
    Welcome to the board.



    Yes, it is, and has been suggested several times in this thread: Don't use Select. It's unnecessary and slows your code.
    I wrote how to avoid the error WITH Select - not WITHOUT.

  9. #19
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,845
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

    Well, in that case, you succeeded in repeating the suggestion of the original, 13-year old post. Good to know it still works, thanks.

  10. #20
    New Member
    Join Date
    Nov 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Method Of Range Class Failed (SOLVED)

      
    daisydogflutist, any chance you'd share your code? I'm trying to do almost the same thing. Thanks!!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com