Copy single cell

In this example, the text "Hello" will be copied from cell A1 to cell B1.

In the top of Excel, select Developer and then select Visual Basic. In the left panel, double-click Sheet1, and enter the following markup in the right panel. 

Public Sub myCopy()
    Range("A1").Copy Destination:=Range("B1")
End Sub

Click on the green play icon, and the word Hello should be copied from A1 to B1.

Copy multple cells

In this example, there is text in cells A1:A6.

Update your VBA so that range is A1:A6 and B1:B6.

Public Sub myCopy()
    Range("A1:A6").Copy Destination:=Range("B1:B6")
End Sub

Click on the green play icon, and cells A1:A6 should be copied to B1:B6.

Copy to another sheet

In this example, we want to copy A1:A6 from Sheet1 to B1:B6 on Sheet2.

Update your VBA so to include Sheet1 and Sheet2.

Public Sub myCopy()
    Worksheets("Sheet1").Range("A1:A6").Copy Destination:=Worksheets("Sheet2").Range("B1:B6")
End Sub

Click on the green play icon, and cells A1:A6 should be copied to B1:B6 on Sheet2.