How to Paste a Range onto another worksheet with filters on

Question

This seems like a simple task but I keep running into various errors. I need to filter worksheet B and then copy a column of data. I then need to filter worksheet A and then paste the copied data into a column.

Worksheets("SheetB").Select

lastRowOne = Range("B" & Rows.Count).End(xlUp).Row
Range("DL2:DL" & lastRowOne).AutoFilter Field:=116, Criteria1:="<>Apples"



 lastRowTwo = Range("B" & Rows.Count).End(xlUp).Row
 Range("DG2:DG" & lastRowTwo).AutoFilter Field:=111, Criteria1:=Target

'Target is already defined earlier in the Macro and functions fine


 lastRowThree = Range("B" & Rows.Count).End(xlUp).Row
 Range("DX2:DX" & lastRowThree).Copy


Worksheets("SheetA").Activate


lastRowFour = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & lastRowFour).AutoFilter Field:=1, Criteria1:=Target


lastRowFive = Range("B" & Rows.Count).End(xlUp).Row

Range("Z2:Z" & lastRowFive).SpecialCells(xlCellTypeVisible).Select

 Selection.PasteSpecial Paste:=xlPasteRange, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

In place of the last line I have also tried:

ActiveSheet.Paste

The first returns a "Run-time error '1004': PasteSpecial method of range class failed

the ActiveSheet.Paste returns a "Run-time error '1004': Paste method of Worksheet class failed

Although this code is not the cleanest, it all functions with the exception of the "pasting" onto 'sheetA' in Column Z. I also need the data pasted into AA if that can be included in a fix.

Thanks !

I have a hunch that it's due to your use of .Select/.Activate. Using these is not best practice - you want to avoid using .Select/.Activate, as you can see it can throw some errors. I highly suggest reading through that page, and apply it. If you still get errors, let us know. Especially when switching in between sheets, using .Activate will likely fail. — BruceWayne
@BruceWayne Thank you -- I altered my macro to use: lastRow = Range("B" & Rows.Count).End(xlUp).Row Dim Adjustment As Range 'Set Adjustment = Worksheets("Sheet").Range("DX2:DX" & lastRow)' Then Activated and filtered Sheet A then this code: Range("Z2:Z" & lastRowTwelve).SpecialCells(xlCellTypeVisible).Value = Adjustment.Value This "pasted" in the correct column BUT the data that was "copied" was incorrect. It ignored the filters on SheetB (in my original code it was copying correctly. Any Suggestions? I am continuing to try and clean out the .activate and .selection! — RugsKid
I changed to: Set Adjustment = Worksheets("YTD Promo Review").Range("DX2:DX" & lastRowTen).SpecialCells(xlCellTypeVisible) This resulted in four correct lines bring "pasted" to SheetA in Column Z. But the rest of the rows below the top four are not correct. They say #N/A — RugsKid
@BruceWayne Thank you for the help. The code you posted runs without error but the "pasted data" is incorrect. It is pulling from the correct column on Sheet B but the incorrect rows. It is "copying" from the top of the column instead of only the visible cells (from the filter). I changed: Set copyRng = .Range("DX2:DX" & lastRow) to: Set copyRng = .Range("DX2:DX" & lastRowLast).SpecialCells(xlCellTypeVisible) This "copied" and "pasted" the first four rows correctly but the rest of the rows now contain #N/A - just as a value - no formula in them. Any ideas? — RugsKid
It's hard to tell without seeing the data, especially when filters are added. Are these two ranges equal for sure? Before it copies, look at the copyRng and see if there's any reason a cell might be/become #N/A. Are these formulas I assume that you're pasting the values to the other sheet? If you can, I suggest doing this part of the steps manually, with the macro recorder on. So, copy your range, paste as values to the other sheet. Then look at the code generated, and also check to see if there are any #N/A cells. (PS: When replying regarding my answer, comment under that one) — BruceWayne

BruceWayne BruceWayne · Accepted Answer · 2016-09-15T15:27:21

Here's (I hope) the same macro, but without .Select/.Activate, and a little tweaking. For instance, you don't need more than one "lastRow" variable. Since you really just reset it, you can use one.

Sub tester()
' First create, then SET, worksheet variables to hold the sheets. We use these when
' referring to ranges, cells, etc.
Dim aWS As Worksheet, bWS As Worksheet
Set aWS = Worksheets("SheetA")
Set bWS = Worksheets("SheetB")

Dim lastRow As Long 'AFAICT, you only need this one Last Row variable. Just update it each time.
Dim copyRng As Range

With wsB ' working with SheetA
    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    .Range("DL2:DL" & lrOne).AutoFilter Field:=116, Criteria1:="<>Apples"

    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    .Range("DG2:DG" & lastRow).AutoFilter Field:=111, Criteria1:=Target

    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    ' We now SET the range we want to copy. We can avoid copy/paste by setting two ranges equal
    ' to eachother. For now, let's store the COPY RANGE in a Range variable
    Set copyRng = .Range("DX2:DX" & lastRow).SpecialCells(xlCellTypeVisible)

End With 'bWS

Dim pasteRng As Range
With aWS
    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    .Range("A2:A" & lastRow).AutoFilter Field:=1, Criteria1:=Target

    lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set pasteRng = .Range("Z2:Z" & lastRow).SpecialCells(xlCellTypeVisible)
End With 'aWS

pasteRng.Value = copyRng.Value

End Sub

The only hesitation I have is the pasting to SpecialCells. AFAIK, if the paste range is different than the copy range, you might get some errors. In any case, try the above and let me know what happens.

An important thing to pay attention to, especially when using multiple worksheets, is that you should be explicit with which sheet you want to get a Range(),Cells(),Rows(),Columns(),etc. Otherwise, it's going to get that info. from the ActiveSheet, whatever that may be.

How to Paste a Range onto another worksheet with filters on

1 Answers