How to Copy Range of Filtered Data and Paste it to new work sheet in Excel VBA (without Using Clipboard)

baskar Published at Dev

baskar

I want to Copy ColumnA and B to New sheet Range A and B (Source Sheet Filter Applied in ColumnH)

This code is Recorded one, When i use this Random Error comes. Because i have 5 submacro and When i call these sub macro its not run correctly. But individual Macro run perfectly.

So i need to Copy without use of Clipboard like this method. Its not use when filter applied condition

Sheets("GROUP1").Range("A:B").Value = Sheets("Sheet3").Range("A:B").Value

Recorded Macro

Sub Copypaste()
'Application.ScreenUpdating = False

    Sheets("GROUP1").Select

    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:H1").Select
    Range("H1").Activate
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A:$H").AutoFilter Field:=8, Criteria1:="K-True", Operator:=xlFilterValues
    Columns("A:B").Select
    Application.CutCopyMode = False
    Selection.Copy

       DoEvents
    Sheets("Sheet3").Select
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Edu Garcia

What you could do is loop through the source sheet checking by Cell.RowHeight > 0 then setting DestinationCell.Value = SourceCell.Value. Ex.:

Sub Copypaste()
    Dim lRow As Long, lLastRow As Long, LRowCount As Long

    Sheets("GROUP1").Select

    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:H1").Select
    Range("H1").Activate
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A:$H").AutoFilter Field:=8, Criteria1:="K-True", Operator:=xlFilterValues        
    lRowCount = 1
    lLastRow = ActiveSheet.Cells.SpeciallCells(xlCellTypeLastCell).Row

    For lRow = 1 to lLastRow

        If ActiveSheet.Range(lRow).RowHeight > 0 Then
            Sheets("Sheet3").Range("A" & lRowCount & ":B" & lRowCount).Value = ActiveSheet.Range("A" & lRowCount & ":B" & lRowCount).Value
            lRowCount = lRowCount + 1
        End If

    Next

End Sub

P.S.: If you have any issues, please let me know.

Collected from the Internet

Please contact [email protected] to delete if infringement.