How to to paste inside for loop?

[dmadhup]

Hi,

I am seeking help pasting the copied value inside for loop in the following code. But complaining at line:

.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Any help, please. thanks

Dim lastIrow As Long
lastIrow = Cells(Rows.count, "I").End(xlUp).Row
Dim i As Long, j As Long, cl As Range, rng As Range, add As Range
For i = 6 To lngEndRow Step 1
            With Sheets("Sheet1")
                .Range("N" & i & ":U" & i).Copy
            End With
            Worksheets("Sheet2").Activate
            
            ActiveSheet.Range("B17:AB50").AutoFilter Field:=8, Criteria1:=Range("I16")
            
            Set rng = Range("K18:K" & lastIrow).SpecialCells(xlCellTypeVisible)
           
            For Each cl In rng
                 .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Next cl


    End With


Next i

 

[shg]

Where do you want to paste those cells values? The code doesn't say.

 

[dmadhup]

I want to paste to c1

 

[dmadhup]

I have also tried this to paste on K cells but no luck.

            Dim last As Long
            For Each cl In rng
                last = Cells(Rows.count, "K").End(xlUp).Row
                Range("K" & last).Select
                 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Next cl

 

[dmadhup]

I solved the problem using Tom answer from this link:

https://www.mrexcel.com/forum/excel-questions/80529-pastespecial-method-range-class-failed.html

Thanks