Methods to transfer data from Excel worksheet with VBA

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