July 22, 2016

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

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

  1. Hello Dinesh,
    Firstly I would like to thank you for making my work much easier!
    In your most recent video (https://www.youtube.com/watch?v=f3v_pIaGrhU&feature=em-subs_digest), 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. Hi Dinesh


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

    Can you help me.


  3. Hi Dinesh,

    I’ve been needing some help in creating a vba to do the following –

    1. My source data is a file that gets updated every month, in most instances the number of columns remain the same but in case the number of columns change, I need to be able to pick the new columns up
    2. The rows in the source data change – while the data is dated, it is not necessarily ordered by date; the source data starts from A5 (A4 is the header) and rows A1 – A3 are merged with a title (this is of no use to me); however, I do not want to perform any manipulations to the source data

    Normally, I would open the source data file and paste into my formula file in a sheet titled Master Data. However, as you are aware any process done manually is prone to error and I was hoping to achieve the following –

    1. Clear everything on the master file except for the header row
    2. Using a button – without opening the source file, start pasting the data from cell A2 and below
    3. In case of any additional columns, I would like to insert columns instead of them just being pasted over (because in the master data, I have columns to the right of the data which have some formulas and I woudn’t want them to be over-written).

    I have very limited knowledge of VBA – any help would be greatly appreciated

Comments are closed.