excel vba copy range code example
Example 1: how to copy a cell in vba
Public Sub CopyRows()
Sheets("Sheet1").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To FinalRow
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