0

I am new to macros. I have this below code in which I am trying to copy some filtered data from one sheet and paste in another worksheet in the end but getting error in the pasting step. I dont know how to correct that. Can someone please help me on this?

Sub MyTest()
    Dim ws1 As Worksheet        
    Dim ws2 As Worksheet       
    Dim i As Long        
    Dim j As Long        
    Dim LastRow As Long        
    Dim r As Long        
    Dim str As String        
    Dim lRow As Long
    
    Application.ScreenUpdating = False
    
    Set ws1 = Sheets("Sheet2")        
    Set ws2 = Sheets("Sheet1")        
    lr1 = Cells(Rows.Count, 3).End(xlUp).Row        
    lr2 = Cells(Rows.Count, 2).End(xlUp).Row
    
    For r = lr1 To 5 Step -1        
        ws2.Activate        
        str = ws2.Cells(r, "C")        
        i = Application.WorksheetFunction.CountIf(ws1.Columns(1), str)
    
        If i > 1 Then ws2.Rows(r + 1 & ":" & r + i - 1).Insert 
   
        ws2.Range(Cells(r, "C"), Cells(r + i - 1, "C")) = str        
        ws2.Activate        
        ws2.Range("$A$4:$W$4").AutoFilter Field:=3, Operator:=xlFilterValues, Criteria1:=str        
        ws1.Activate        
        ws1.Range("$A$1:$D$1").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=str
        ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select        
        Range(Selection, Selection.End(xlToRight)).Select        
        Range(Selection, Selection.End(xlDown)).Select        
        Selection.SpecialCells(xlCellTypeVisible).Copy        
        ws2.Activate        
        ws2.Range("$A$4:$W$4").AutoFilter Field:=3, Operator:=xlFilterValues, Criteria1:=str
        ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 8).Select        
        Selection.PasteSpecial Paste:=xlPasteValues        
        Application.CutCopyMode = False     
    Next r  
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
GAJENDRAN G
  • 1
  • 1
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Please tell which error you get and in which line. – Pᴇʜ Jun 15 '21 at 14:10
  • I am getting error in the pasting step. "Selection.PasteSpecial Paste:=xlPasteValues". – GAJENDRAN G Jun 15 '21 at 15:49
  • Error msg is - "Pastespecial method of range class failed" – GAJENDRAN G Jun 15 '21 at 15:51
  • I am trying to copy filtered data from sheet 2 and paste in the first visible cell of "H" column after the autofilter. – GAJENDRAN G Jun 15 '21 at 15:54
  • I recommend to read the link I gave you and use this technique to get rid of all your `.Select` and `.Activate` statements then. – Pᴇʜ Jun 15 '21 at 18:40

0 Answers0