Often we need to transfer data from an Excel worksheet into another range, worksheet or workbook. We can automate the transfer process with VBA. Watch the training video below:

Watch the video on YouTube.

Here’s the code to perform different types of transfers:
Sub rangeCopy()
‘Range(“B2”).Copy Range(“C2”)
‘Range(“A1:B6”).Copy Range(“F1:G6”)
‘Range(“A2:B2,A6:B6”).Copy Sheet2.Range(“A1”)
‘Worksheets(“Sheet1”).Range(“A1:B1,A2:B2,A6:B6”).Copy Worksheets(“Sheet2”).Range(“A1”)
‘Workbooks(“different-ways-to-automate-copy-paste-with-vba.xlsm”).Sheets(“Sheet1”).Range(“A1:B1,A2:B2,A6:B6”).Copy Workbooks(“destination.xlsx”).Worksheets(“Sheet2”).Range(“A1”)

‘Range(“D2”).Value = Range(“B4”).Value
‘Range(“G1:G6”).Value = Range(“A1:A6”).Value
‘Sheet2.Range(“G1:G6”).Value = Sheet1.Range(“B1:B6”).Value
‘Workbooks(“destination.xlsx”).Worksheets(“Sheet2”).Range(“A1:B6”).Value = Workbooks(“different-ways-to-automate-copy-paste-with-vba.xlsm”).Sheets(“Sheet1”).Range(“A1:B6”).Value
Sheet1.Range(“A1:B6”).Copy
Sheet2.Range(“I4:N4”).PasteSpecial Transpose:=True
‘Next line of code removes the ant-like structure surrounding the copied data
Application.CutCopyMode = False
End Sub

Further reading:

3 Methods to Copy & Paste with VBA