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
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

3 thoughts on “Methods to transfer data from Excel worksheet with VBA

  1. Xavier

    Hello Dinesh,
    Firstly I would like to thank you for making my work much easier!
    In your most recent video (, you are showing very close to what I need to do where I work, but just a minor change.. can you please include the FIND function ?? The accounting department has only partial account names, as an example:
    Sales for California 350 (The 350 is only the partial account number which I need to look for and add to a cell in a different workbook).
    Your help is very much appreciated !!

  2. Arif Merchant

    Hi Dinesh


    I would like to know the formulae in Excel to calculate the hierarchy commission for Binary systems.

    Can you help me.



Leave a Reply

Your email address will not be published. Required fields are marked *