vba copy range to another sheet code example

Example 1: how to copy a cell in vba

Public Sub CopyRows() 
    Sheets("Sheet1").Select 
    ' Find the last row of data 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row 
    For x = 2 To FinalRow 
        ' Decide if to copy based on column D 
        ThisValue = Cells(x, 4).Value 
        If ThisValue = "A" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetA").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        ElseIf ThisValue = "B" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetB").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        End If 
    Next x 
End Sub

Example 2: excel vba copy range with filter

Sub ExtractVisibleCells()

    Dim wsSource, wsExtract As Worksheet

    Set wsSource = ThisWorkbook.Sheets("Source Data")
    Set wsExtract = ThisWorkbook.Sheets("Filtered")

    wsSource.Range("A1:A10").SpecialCells(xlCellTypeVisible).Copy
    wsExtract.Cells(1, 1).PasteSpecial

End Sub