CODEWITHSUNDEEP
excelvba
Carissa De Vera
Carissa De Vera

Reputation: 35

How to Copy-PasteSpecial cells from filtered range

Here's my code below.

Sub AddExistingItemToRWP()
Dim AddRow As Integer
Dim eLastRow As Integer
AddRow = Worksheets("Recipe Workarea-Product").Range("A" & Rows.Count).End(xlUp).Row
eLastRow = Worksheets("Additional Existing Raw Mat.").Range("A" & Rows.Count).End(xlUp).Row
Dim Rng As Range
Sheets("Additional Existing Raw Mat.").Select
Set Rng = ActiveSheet.AutoFilter.Range
With Sheet12
    With .Range("$A$1:K" & eLastRow)
         .AutoFilter Field:=11, Criteria1:=("Y")
         .SpecialCells (xlCellTypeVisible)
         .Offset(1, 0) _
         .Copy Destination:=Sheet8.Range("H" & AddRow + 1)
         .PasteSpecial Paste = xlPasteValues

    End With
End With
AutoFillCols (AddRow)
Sheets("Additional Existing Raw Mat.").Select
End Sub

The .pastespecial cells seems to be not working. What is the correct syntax for this?

Upvotes: 1

Views: 14571

Answers (2)

Carissa De Vera
Carissa De Vera

Reputation: 35

I finally got the solution to my problem. Here's my code:

Sub AddExistingItemToRWP()
Dim AddRow As Integer
Dim eLastRow As Integer
AddRow = Worksheets("Recipe Workarea-Product").Range("A" & Rows.Count).End(xlUp).Row
eLastRow = Worksheets("Additional Existing Raw Mat.").Range("A" & Rows.Count).End(xlUp).Row

Dim Rng As Range

Sheets("Additional Existing Raw Mat.").Select
Set Rng = ActiveSheet.AutoFilter.Range
With Sheet12
    With .Range("$A$1:K" & eLastRow)
         .AutoFilter Field:=11, Criteria1:=("Y")
         .SpecialCells(xlCellTypeVisible).Select
         Selection.Offset(1, 0).Copy
         Sheets("Recipe Workarea-Product").Select
         Range("H" & AddRow + 1).Select
         Selection.PasteSpecial Paste:=xlPasteValues

    End With
End With

AutoFillCols (AddRow)
Sheets("Additional Existing Raw Mat.").Select

End Sub

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53135

Four things:

  1. .SpecialCells(xlCellTypeVisible) returns a reference to a range, but you don't use it
  2. You can't use both Destination:= ... and .PasteSpecial with one Copy. Choose one.
  3. You mean .PasteSpecial Paste:=xlPasteValues not .PasteSpecial Paste = xlPasteValues
  4. You activate and filter sheet "Additional Existing Raw Mat.", then refer to a filter on Sheet12. Are you sure thats right?

Update: How to use Copy PasteSpecial

.Copy 
Sheet8.Range("H" & AddRow + 1).PasteSpecial Paste:=xlPasteValues

Upvotes: 2

Related Questions