Object doesn't support this property or method-selection.paste

[Dustywrinklemen]

Here is part of my code. I want it to select the value in the next column over if the original value meet the criteria.

Sub Macro1()
Range("d4").Select
Do
If Selection.Value < 13 Then
ActiveCell.Offset(0, -1).Select
Selection.Copy
Sheets("ela sgo").Select
Range("M1").Paste
Else
Selection.Offset(1, 0).Select
End If
Loop


End Sub

I am getting the error (object doesn't support this property or method) for Selection.paste

Probably an easy fix or something's wrong with the top part but I'm new to all this, help!

 

[patel45]

try Range("M1").PasteSpecial
or

Sub Macro1()
Range("d4").Select
Do
   If Selection.Value < 13 Then
         ActiveCell.Offset(0, -1).Copy Sheets("ela sgo").Range("M1")
   Else
         Selection.Offset(1, 0).Select
   End If
Loop
End Sub

 

[Jon von der Heyden]

Welcome to the board!

I don't think it's necessary to loop. You can use a filter to grab all values > 13 and then paste the adjoining range values to the destination in one hit.

Also, you paste operation would keep overwriting M1 in the "ela sgo" sheet, essentially leaving you with the last item only.

Perhaps;

Public Sub Demo()
    Dim rngToCheck As Excel.Range
    Dim rngCell As Excel.Range
    
    With ActiveSheet
        'try to refer to the range explicitly rather than loop through EVERYTHING
        'include the label so that we can use AutoFilter method to grab all values in one hot
        Set rngToCheck = .Range("D3:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
        .AutoFilterMode = False
    End With
    
    'use AutoFilter to grab all values > 13 and then paste results in one hit
    With rngToCheck
        Call .AutoFilter(Field:=1, Criteria1:=">13")
        Call .Offset(1, -1).SpecialCells(xlCellTypeVisible).Copy(Destination:=Sheets("ela sgo").Range("M1"))
        Call .AutoFilter
    End With
End Sub

 

[Dustywrinklemen]

Jon, I tried using the code you provided and got a compile error: named argument not found. I'm not sure if I wrote it in wrong but I double checked to make sure it was exactly as written and it looked fine. The problem is a lot of codes in that are way above my knowledge of VBA. Perhaps you know of an online course or set of videos that's worth checking out? I've found another thread on Mr. Excel but it had about 5000 recommendations and I was a bit overwhelmed.

Patel45, I tried you suggestion and it took an extremely long time to calculate what is a pretty small amount of data and left me with only one value in the M1 spot (as Jon mentioned).

Basically what I want to do is check a column of numbers, see if they meet a criteria. If they do I want to copy the corresponding name two columns over and paste that value in a specific spot on another sheet.

Thanks both for taking time on this!

 

[Jon von der Heyden]

My code seems good but I confess I haven't tested it. I'll test it later when I am back on my machine. In the meantime please let me know what line the code breaks on when you execute it.

Also, what range does the numbers occupy (column D, but what rows)...?

 

[Dustywrinklemen]

I entered d3:d100 as the range it should filter through. It appears to be working now (no error messages) but no values are pasted on the "ela sgo" sheet.

 

[MARK858]

I haven't tested the code but have you changed Jon's >13 to your <13

 

[Dustywrinklemen]

The code worked great! It was my fault because the values that were being copied were a concatenate function, and were thus pulling nothing in the other sheet. Once I pasted that cell range as values it worked. Thanks everybody!