PasteSpecial not pasting, but code does not error

Question

At this point there are two problems, but the first one i want to deal with is that i cannot get the paste function to work. When I run through the code the specific cells are highlighted to copy (the cell border is b&w flashing) and the cells where they are to end up are now highlighted, but nothing pastes.

Sub OtherTask()
Dim DRng As Range

ActiveSheet.Range("g2:ah2").find(Date).Select
ActiveCell.Resize(5).Offset(5).Select
Selection.AutoFilter field:=1, Criteria1:="1", Operator:=xlFilterValues

Set DRng = ThisWorkbook.ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
DRng.Copy
ActiveSheet.Range("r12").PasteSpecial xlPasteAll

    If ActiveSheet.AutoFilterMode = "True" Then
        ActiveSheet.AutoFilterMode = "False"
    End If

End Sub

I should bring up the second problem. When I execute this from the macro button it performs as per the description above, but when I am in the editor and I press the play button I get error 91 that the object is not set. Not sure why I would get the error with one form of execution and not the other?? Looking through similar perhaps I should be using value instead of copy? Thanks for any help.

Read about [Explicitiy Qualifying References](http://stackoverflow.com/documentation/excel-vba/1576/common-mistakes/5110/qualifying-references) and this and many future problems will be solved :) — Scott Holtzman, Mar 13 '17 at 18:50
thanks, I came across that when posting as a suggestion. In my case I believe i have put the recommendation "Move ... .Copy just before line where you paste" in this code. If there is another lesson there I apologise that I cannot see it, unless it to use Value instead of Copy. At this point I would really like to get Copy understood before going forward. Thanks though, i'll keep looking at it. — officemanager2, Mar 13 '17 at 18:59
The teaching I am offering is to remove `ActiveSheet`, `ActiveCell`, `Selection` etc. and *explicity* qualify and work with the desired object. Also, [remove the `Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) statements and work directly with the explicit objects. Lastly - and in conjunction with above - the AutoFilter method does not have an explicit range either. Fixing these issues **will solve what** *you think* **the issue is** — Scott Holtzman, Mar 13 '17 at 19:13
Not sure if you can add anything to this. I appreciate the attempt at helping but the With, End With cannot accept Resize, or Offset. Searching here and online it seems the majority of examples when it comes to With, End With deal with changing the font. I also cannot find direction on how to Set the variable to use the With, End With criteria. Any suggestions? — officemanager2, Mar 13 '17 at 21:06

score 0 · Answer 1 · answered Mar 14 '17 at 02:31

I had to make some assumptions with your code because there are some things that are not that clear. The assumptions should be easy to see and to change according to your needs.

Sub OtherTask()

Dim ws as Worksheet
Dim DRng As Range

Set ws = Worksheets("mySheet")

With ws

    Dim rFound as Range 
    Set rFound = .Range("g2:ah2").find(Date)

    rFound.Resize(5).Offset(5).AutoFilter field:=1, Criteria1:="1", Operator:=xlFilterValues

    'declare this range explicitly, whatever it is
    Set DRng = .Range("A1:B5000").AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    DRng.Copy .range("R12") 'since you paste everything just do straight from copy method

    If .AutoFilterMode = "True" Then .AutoFilterMode = "False"

End With

End Sub

Thanks for this. The issues i am having are becoming less muddled but i'm afraid with this code the 'Set DRng' is still coming up as DRng = Nothing when I walk through the code. I updated the range and the worksheet name, but still getting that message. Set rFound does come up with the correct result which is today's date. I did notice when walking through the line that uses resize and offset does not get acknowledged in the worksheet once the line is executed. Perhaps this is a result of not using .select ?? I'll keep working at it. — officemanager2, Mar 15 '17 at 17:35

PasteSpecial not pasting, but code does not error

1 Answers1