Method Of Range Class Failed (SOLVED)

[Elemental]

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.

 

[nmgmarques]

Hi
Avoid using Select at all:

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:

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

 

[RoryA]

Try something like this:

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

 

[nmgmarques]

Try something like this:

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"

 

[RoryA]

Oops - remove the xlPasteAll

 

[Monique Delmolino]

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!

 

[darchello]

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.

 

[shg]

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.

 

[darchello]

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.

 

[shg]

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

 

[macros44]

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