Copy Data from One Range to Another in Excel VBA

Home/Excel VBA/Copy Data from One Range to Another in Excel VBA

Description:

Copying Data from One Range to Another range is most commonly performed task in Excel VBA programming. We can copy the data, Formats, Formulas or only data from particular range in Excel Workbook to another range or Sheet or Workbook.


This example to show you how to copy data from one Range to another using Excel VBA. This is one of the frequently used codes in the VBA, we often do this activity, copying the data from one range to another range in a worksheet.r

Copy Data from One Range to Another in Excel VBA- Solution(s):

Copy Data from One Range to Another in Excel VBAYou can use Copy method of a range to copy the data from one range to another range.

Copy Data from One Range to Another in Excel VBA – An Example

The following example will show you copying the data from one range to another range in a worksheet using Excel VBA.

Code:
'In this example I am Copying the Data from Range ("A1:B10") to Range(E1")
Sub sbCopyRange()
'Method 1
Range("A1:B10").Copy Destination:=Range("E1")
'Here the first part is source range, 
'and the second part is target range or destination.
'Target can be either one cell or same size of the source range.
'Method 2
Range("A1:B10").Copy
Range("E1").Select
ActiveSheet.Paste
'In the second method, first we copy the source data range
'Then we select the destination range
'Target can be either one cell or same size of the source range.
'Then we paste in the active sheet, so that it will paste from active range
Application.CutCopyMode = False
End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in Sheet1 at A1:B10
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to run it

Now you should see the required data (from Range A1 to B10 ) is copied to the target range (Range E1 to F10).

By |June 14th, 2013|Excel VBA|37 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

37 Comments

  1. rajaudaiyar March 31, 2014 at 3:18 PM - Reply

    How to copy excel cell to excel another cell

    • PNRao April 1, 2014 at 9:46 AM - Reply

      Hi,
      You can use Cells(1,1).Copy Destination:=Cells(1,2) to copy the data from one cell to another cell, it is same as copying a range from to another range.

      Thanks-PNRao!

  2. Amit Shah October 30, 2014 at 7:07 AM - Reply

    Hi, please help me on my data. Cell A1 has a roll number and cell C1 has the name. But my data is not continues. There are blanks and some miscellaneous data also. I want is, if A1 has roll number then copy the name from C1 and paste in F1 cell. Please help me with the coding.

    Thanks
    Amit

  3. Galo November 20, 2014 at 3:36 AM - Reply

    Hello, please your help.

    How do I copy a range of cells from workshhet1 to worksheet2 based on the name of a column in worksheet1?

    For example I have in WS1 a column named “Items”. I want the macro in WS2 to look for the column “Items” in WS1 and copy such column´s data in WS2.

  4. Amol January 5, 2015 at 1:38 PM - Reply

    Its working perfect , but my cells contain formula in it and this methods not working to move data from cells have formula in it . please help me in this .

    • PNRao January 6, 2015 at 8:04 PM - Reply

      Hi Amol,

      When you have the formulas in the Cell, you can do the paste special as values: Here the Simple example to copy the data (cells or range) with formulas:

      Range(“B5:B6”).Copy
      Range(“J5”).PasteSpecial (xlPasteValues)

      Thanks-PNRao!

  5. Jun Rizalde January 19, 2015 at 3:28 PM - Reply

    Hi Valli or Anyone,

    Please help, On sheet-1 I have an data entry field. I want my macro to copy any data I place on the cell on sheet-1 and paste it to the cells in sheet-2. I works on the first line however, I want the next data to copied and pasted on the next Row in sheet-2

    Example:
    Sheet 1 Row1 Column 1 Data Field: JUN
    Sheet 2 Row1 Column 1: JUN
    Sheet 1 Row1 Column 1 Data Field: RAJAUDAIYAR
    Sheet 2 Row2 Column 1: RAJAUDAIYAR
    Sheet 1 Row1 Column 1 Data Field: PNRAO
    Sheet 2 Row2 Column 1: PNRAO
    Sheet 1 Row1 Column 1 Data Field: AMIT SHAH
    Sheet 2 Row3 Column 1: AMITH SHAH

    • PNRao January 19, 2015 at 6:42 PM - Reply

      Hi Jun,
      It seems like you want to synchronize the Worksheet2 with Worksheet1.
      Place the below code in the Sheet1 Code module:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Sheets("Sheet2").Range(Target.Address) = Range(Target.Address)
      End Sub
      

      Please feel free to ask me if you are looking for something else.

      Thanks-PNRao!

  6. Jun Rizalde January 20, 2015 at 12:27 PM - Reply

    Hi PNRao,

    I tried using the script you gave but I encountered errors.

    Here is what I wanted to do actually.

    In sheet1 cell A1 I am using it as a entry field. Let say I type in your name PNRao then I will have a click button that will paste it to Sheet2 cell A1. Then I will type in my name on shee1 cell A1 Jun and click on the button. I will then paste my name on Sheet2 cell A2.

    My statement may be confusing, sorry.

    • PNRao January 23, 2015 at 10:43 AM - Reply

      Hi Jun,

      Assign this macro to your button click:

      Sub btnClikToEnter()
      'Find last used row in sheet 2
      lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
      lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
      Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
      lRow = lRow - 1
      Loop
      lastRow = lRow
      If Sheets("Sheet2").Range("A1") = "" And lastRow = 1 Then
      Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
      Else
      Sheets("Sheet2").Range("A" & lastRow + 1) = Sheets("Sheet1").Range("A1")
      End If
      End Sub
      

      Thanks-PNRao!

      • Priya K August 5, 2016 at 12:51 PM - Reply

        hi

        I used the code provided by you as i have similar query. But what it does is it only copies into rows and then whatever entered gets overwritten in row 2 of sheet2.

  7. boskey March 10, 2015 at 1:04 PM - Reply

    hi,
    i m working in solar industry, i have a problem related to data.i have two excel file i st have the particular data with data and second one containing all year data.so i have to merge means particular dates data from excel 2 to excel 1 ..how do i do thisin easy manner

  8. Khaled March 13, 2015 at 9:09 AM - Reply

    please check the last row in the following code, it contains error but I cant find

    Sub copyDataFromMultipleWorkbooksIntoMaster()

    Dim FolderPath As String, Filepath As String, Filename As String

    FolderPath = “C:\work\excel_tutorial\suppliers\”

    Filepath = FolderPath & “*.xls*”

    Filename = Dir(Filepath)

    Dim lastrow As Long, lastcolumn As Long

    Do While Filename “”
    Workbooks.Open (FolderPath & Filename)
    ‘Range(“A2:D2″).Copy
    lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
    lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
    Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.Close

    erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

    ‘If we wanted to paste data of more than 4 columns we would define a last column here also
    ‘lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
    ‘ActiveSheet.Paste Destination:=Worksheets(“Sheet1″).Range(Cells(erow, 1),
    Cells(erow, lastcolumn))
    ActiveSheet.Paste Destination:=Worksheets(“Sheet1″).Range(Cells(erow, 1),
    Cells(erow, 4))

    Filename = Dir

    Loop

    End Sub

    • PNRao March 21, 2015 at 2:45 PM - Reply

      You can use below syntax to copy and paste:

      Method1:
      Range(“YourSourceRange”).Copy Destination:=Range(“YourDestionationRange”)
      Example:
      Range(“A1:D5”).Copy Destination:=Range(“E5”)

      Method 2:
      Range(“YourSourceRange”).Copy
      Range(“YourDestionationRange”).Select
      Activesheet.Paste
      Example:
      Range(“A1:D5”).Copy
      Range(“E5”).Select
      Activesheet.Paste

      Hope this helps! Thanks-PNRao!

  9. jen March 15, 2015 at 10:36 PM - Reply

    Hi,

    I want to copy data from multiple cells of one sheet into a single cell in another sheet,
    For example is cells A1- apple
    B1 – orange
    C1 – grape.
    I want cell A1 in sheet 2 to have apple,orange,grape

    How to write macor for this please

    • PNRao March 21, 2015 at 2:51 PM - Reply

      Hi Jen,
      You can use the below code:

      Sub sbConcatenateCells()
      Sheet2.Range("A1") = Sheet1.Range("A1") & "," & Sheet1.Range("B1") & "," & Sheet1.Range("C1")
      End Sub
      

      Thanks-PNRao!

  10. Sanchit March 16, 2015 at 2:54 PM - Reply

    Hi,
    I used the above method to insert the columns from another sheet. It worked perfectly well.

    I used the button click to copy columns from another sheet. Now I want to insert checkboxes against those copied columns on the option click.
    Also I want the checkbox to be checked in one case and unchecked if in another case.
    Please help me with that!!!

  11. uday April 7, 2015 at 1:18 PM - Reply

    Hi All,

    Can anyone help me in printing data from one filed to another field using a button in the same worksheet

    Thanks

  12. Dragony2000 April 12, 2015 at 10:12 AM - Reply

    I make find code to select multiple cells i wanna copy the selected cells to another rows in the same column

    i mean if searching catch C1 , M1 , R1 it copies automatically in ( C5, M5, R5) and fill until (C9, M9 , R9)

  13. Teecee April 29, 2015 at 2:30 PM - Reply

    I would like to add a function to my Excel workbook something like as follows:
    set(destination,source) {destination.value=source.value; current_cell.value=source.value}

    The current cell would be the cell that contains the usage of the “set” function along with the needed arguments. It’s output result isn’t actually necessary though, what is important is the ability to transfer values to another cell range.

  14. khine su win May 13, 2015 at 1:32 PM - Reply

    How to copy excel file data only by avoiding formula?
    I want to copy only data to another file.
    Please let me know the way.
    Thank you.

    Khine Su Win

    • PNRao May 13, 2015 at 8:46 PM - Reply

      Hi Khine su win,
      Here is the code to copy the range A1 to C10 and paste at D1:
      Paste:=xlPasteValues option allow you to copy only the values and paste.

      Sub CopyTheDataAndPasteOnlyValues() '
      Range("A1:C10").Copy
      Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      End Sub
      

      Thanks-PNRao!

      • Ajay September 2, 2015 at 12:19 PM - Reply

        Hi Sir,

        With below mention code i able to copy data from _Update Sheet To Master Sheet but In _Update Sheet My Data have formula so i not getting the desire result because formula is getting copied in Master Sheet so please help me

        Sub CopyRangeFromSheet1toSheet2()
        Dim lastRow As Long
        lastRow = Sheets(“Master Sheet”).Range(“A100000”).End(xlUp).Row + 1 ‘ then next free row in sheet2
        Sheets(“_Update”).Range(“A3:F3”).Copy Destination:=Sheets(“Master Sheet”).Range(“C” & lastRow)
        End Sub

        Best Regards
        Ajay

  15. rajni kumar May 14, 2015 at 7:30 PM - Reply

    Hi PNRao,
    I have a requirement to move the data from columns into rows, and also I need parameterized the starting point of the columns that are to be converted into rows. Find below my example

    raw data
    FName LName Num1 Num2 Num3
    rajni kumar 10 11 23
    David Betts 5 3 8

    to covert like this
    FName LName Values
    rajni kumar 10
    rajni kumar 11
    rajni kumar 23
    David betts 5
    David betts 3
    David betts 8

    Please can you help or give in some guidance I can try few options.

  16. khine su win May 15, 2015 at 7:39 AM - Reply

    Hi PNRao!

    Thank for your answer.
    Please may i ask another question.
    I want to copy entire excel file but i need only data.
    I mean that wnat to copy data by avoiding formula.
    And then, i also don’t want to open excel file when do copy data.
    Please let me know the way.
    Thank you.

    Khine Su Win

  17. tejas May 15, 2015 at 2:45 PM - Reply

    how to copy the values column wise present in the sheet1 and paste each column values in the different sheets in the workbook.

  18. Tommy May 27, 2015 at 3:52 AM - Reply

    Hello..

    I have 4 columns in sheet 1. I want to match a value in column A, and match a value in column B. Once both values match at a row X, I want to copy column C, rowX to rowX+1 and copy column D, row X to rowX+1 and paste them into a certain cells in Sheet 2.

    Please advice. Thank you,

  19. Nits June 22, 2015 at 9:00 PM - Reply

    Hi,

    I wanted little assistance in the issue I’m facing currently.
    I have 2 sheets, (A) Master Data Sheet (B) Detailed Activities.
    Master Data sheet will have all the process name and there corresponding activities along with some other details in other column. (Basically

    the Database)
    I have 4 columms in Master Data Sheet namely Process name, Activity, Activity Owner and Hours.
    In the Detailed Activities sheet there are multiple coloumns including the columns present in the Master Data Sheet. (i.e. Process name,

    Activity, Activity Owner and Hours.)

    Issue:

    When I enter a process name in the Detailed Activities Sheet in “Process Name”, I want to autopopulate the corresponding Activity, Activity

    Owner and Hours column in the Scheduler Sheet.
    Then when a enter another process name below the previos process name entered the corresponding columns should get auto populated.(As

    per the details mentioned in the Master Data Sheet).

    I made this code but I have given specific range. The following code is only for 1 process name. (Thats why i gave specific range)
    I want to code for atleast 40 processes and I should be able to enter one range below the other.

    For Detailed Activity Sheet
    Column C = Process name
    Column L = Activity
    Column M = Activity Owner
    Column N = Hours

    For Master Data Sheet
    Column A = Process name
    Column B = Activity
    Column C = Acivity Owner
    Column D = Hours

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range(“C2”).Value

    Case “Transfer”

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = Sheets(“Master Data Sheet”).Range(“B2:B12”).Value
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = Sheets(“Master Data Sheet”).Range(“C2:C12”).Value
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = Sheets(“Master Data Sheet”).Range(“D2:D12”).Value
    Sheets(“Detailed Activities”).Range(“D2:D12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“E2:E12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“I2:I12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“J2:J12”).Value = “-”

    Case ” ”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case “”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case Else

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = “Please enter a valid Process Name to update the Activities”
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = “Please enter a valid Process Name to update the Activity Owner”
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = “Please enter a valid Process Name to calculate estimated HRS”

    End Select
    Application.EnableEvents = True
    End Sub

    For transfer process, there are 10 rows of Acitvity, Activity owner and hours.
    So when i enter ” Transfer” in the process column in the Detailed activities sheet, the code should search the keyword “transfer” in the

    master data shet and then copy the corresponding number of rows of Activity, activity owner and hrs from master data sheet to detailed

    activities sheet.
    Then when I enter another process name the same function should be carried out again.

    Please advise.

    Kind Regards,
    Nits.

  20. esskaybee July 1, 2015 at 9:46 AM - Reply

    Sub abcd()
    ‘ Get customer workbook…
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook

    ‘ make weak assumption that active workbook is the target
    Set targetWorkbook = Application.ActiveWorkbook

    ‘ get the customer workbook
    filter = “Text files (*.xlsx),*.xlsx”
    caption = “Please Select an input file ”
    customerFilename = Application.GetOpenFilename(filter, , caption)

    Set customerWorkbook = Application.Workbooks.Open(customerFilename)

    ‘ assume range is A1 – C10 in sheet1
    ‘ copy data from customer to target workbook
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = customerWorkbook.Worksheets(1)

    targetSheet.Range(“A1”, “C10”).Value = sourceSheet.Range(“A1”, “C10”).Value

    targetSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Array(“A123”, “A234”, “A128”, “A129”), Operator:=xlFilterValues, VisibleDropDown:=False

    ‘ Close customer workbook
    customerWorkbook.Close
    End Sub

  21. Preet August 10, 2015 at 5:54 PM - Reply

    I am struggling with copying data from master file to rest of tabs based on filter. Assuming i have master sheet with column name application and my tabs are arranged with column names, i wanted to copy data from master sheet to corresponding tabs of each application, with keeping master data as well. Also if something is present in master tab but not in application tab, that row must be moved to separate tab i created. This tab is common to all application tab. Reason being, this master sheet is going to get updated everyday and i want my tabs to have current data but keeping the old one in misc tab. I will really appreciate the help. Thanks.

  22. Shally June 24, 2016 at 2:59 PM - Reply

    Hello,

    I just started doing macros and I need to do a task which includes changing the values automatically if there is a change in 1 cell. For example if I change a value from 1 to 0 in cell A1 then which ever cell I want to put that 1 from cell A1 automatically goes to the different cell and the value there increases.

  23. SP November 17, 2016 at 10:11 AM - Reply

    Hi, I need to paste different cells in a one sheet to another sheet with same formatting and add the next set of results one below another in sheet 2

  24. Aissa March 8, 2017 at 1:27 PM - Reply

    Thanks for your great answers
    The code works very well and I am in a need to use the same way to copy data from a closed csv file to an active worksheet.

    Would you help?

    thanks

  25. Viral March 21, 2017 at 11:51 AM - Reply

    I have the VBA Code below,

    which ask the use to select the folder from the system, then the code runs and ckecks the excel file, if the folder have the excel file then it will copy and paste the same in the macro file “Copy and paste.xlsx”.

    Now there can be 1 excel file or more than 1.

    Private Sub CommandButton1_Click()
    Sheets(“Sheet1”).Select
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
    .Title = “Select A Target Folder”
    .AllowMultiSelect = False
    If .Show -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & “”
    End With

    NextCode:
    myPath = myPath
    If myPath = “” Then GoTo ResetSettings
    myExtension = “*.xlsx”
    myFile = Dir(myPath & myExtension)
    Do While myFile “”
    Set wb = Workbooks.Open(Filename:=myPath & myFile)

    With Workbooks(myFile)
    With .Range(“a2:BZ” & .Range(“B” & .Rows.Count).End(xlUp).Row)
    .Copy
    End With
    End With

    wb.Close SaveChanges:=False
    myFile = Dir
    Loop
    MsgBox “Task Complete!”
    ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    End Sub

  26. Iva April 14, 2017 at 7:44 PM - Reply

    Hi there:
    I need to copy an entire column to another one but just part of the data in each cell. is it possible to do that?
    For Example:
    Column 1 contains last name, name and I just want to copy last name (which is before the coma) to another range.

    • PNRao April 15, 2017 at 11:59 AM - Reply

      Hi, Can do this by looping through the each row and split the data, and get the first part.

      But this will slow down the process when you have data in more Rows.

      Here is my suggested approach to Copy the Data (First part of the String in a Column) to another Column.

      I am adding a temporary sheet and copy the required column to the temporary sheet. And splitting the Column by Comma. Then Copying the First Column to required sheet and Column.

      Sub sbCopyOnlyFirstPartOfTheColumn()
      'Add a Temporary Sheet
      Set tempSht = Worksheets.Add
      'Copy the required Column to Temporary Sheet
      Sheets("Sheet1").Columns("A:A").Copy Destination:=tempSht.Range("A1")
      'Split the Column Data with Comma Delimeter
      tempSht.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
      :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
      'Now Copy the First Column (first Part) into reqired Sheet and Column
      'This will copy to Sheet2 and Paste at F Column
      tempSht.Columns("A:A").Copy Destination:=Sheets("Sheet2").Range("F1")
      'Delete the Temporary Sheet
      Application.DisplayAlerts = False
      tempSht.Delete
      Application.DisplayAlerts = True
      End Sub
      

      Thanks!

Leave A Comment