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`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), 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, Sep 15 '16 at 14:44
@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, Sep 15 '16 at 14:52
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, Sep 15 '16 at 14:59
@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, Sep 15 '16 at 15:56
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, Sep 15 '16 at 16:05
@BruceWayne the two ranges are not equal (2 rows off). They should be in theory so I will have to look into that. I tried doing this manually and did not get any #N/A values. I did notice that the macro starts in the correct row (then collects 4 correct cells) then the #N/A's begin when the 5th cell in the column is hidden (from the filter). The hidden cell has a more complicated formula in it that involves another macro. — RugsKid, Sep 15 '16 at 16:30
@BruceWayne I found to reason the ranges were off by 2 rows and fixed it. Re-ran the macro and got the same four rows correct then the #N/As. Not sure if this matters but when I manually copied and pasted I did not have to paste special as values. Switching to the other worksheet I was able to simply paste and only values were pasted into the cells. Seems Odd. — RugsKid, Sep 15 '16 at 16:35
@BruceWayne Here is the code the macro recorder captured: `Sheets("SheetB").Select` `Range("DX273:DX401").Select` `Selection.Copy` `Sheets("SheetA").Select` `Range("Z68:Z114").Select` `ActiveSheet.Paste` Notice it simply selected the range entirely but has no code referencing the fact the range has hidden rows. I only copy and pasted 45 rows of data. — RugsKid, Sep 15 '16 at 16:44
Ah - I notice you don't start at row 2, but `273` and `68` - that's not reflected in my macro, or your original code. Could that be the issue? Do we also need to determine a startcell? (Or are these rows gotten by using the `SpecialCells`?) — BruceWayne, Sep 15 '16 at 16:45
@BruceWayne The start point are alright I believe. The macro is finding the startcell to copy alright. It just gets buggered up when it encounters the next hidden row. These start points are being found because they are the first visible cells. — RugsKid, Sep 15 '16 at 17:04

BruceWayne · Answer 1 · 2016-09-15T16:03:16.107

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 Answers1