March 7, 2014

Copy Paste Data from one Excel Worksheet to Another Using VBA

How to copy and paste data from one Excel Worksheet to Another Using VBA

I want to search the whole spreadsheet for the product ‘Car’ and paste the whole row (not just the cell). So in this instance it would be row a2, a5, a8 and a10 from sheet 1 to sheet2.
If I have to use macros to automate the whole process what would be the vba code?

Sub mycar()
‘Let’s start at row 2. Row 1 has headers
x = 2
‘Start the loop
Do While Cells(x, 1) <> “”
‘Look for data with ‘Car’
If Cells(x, 1) = “Car” Then
‘copy the row if it contains ‘Car’
‘Go to sheet2. Activate it. We want the data here
‘Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Paste the data here
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(erow)
End If
‘go to sheet1 again and actvate it
‘Loop through the other rows with data
x = x + 1
End Sub

Watch the Microsoft Excel training video below to see how you can use the macro (vba code) to copy and paste specific data from Sheet 1 to Sheet 2:

23 thoughts on “Copy Paste Data from one Excel Worksheet to Another Using VBA

  1. I want to copy a file from a page,in a page from another file
    ex, file A sheet1 in file B sheet1
    If you can help

    1. You’re absolutely right. It is a waste of your time and energy to hunt up after someone when thr;1#82&7yee not even polite to you. It’s amazing how some people will be in a serious relationship with someone as discourteous and rude like that. But I sometimes give the impression that I am snobby. I’m shy and usually can’t think of much to say in the first couple of encounters. Takes me a while to warm up. I wonder if that’s why I have so many extroverted friends …

  2. hi, i’m loving your page.
    in addition to this, same case but instead i have 2 or more worksheets and i need to copy all rows containing “cars” (following this example) from these sheets to one master file and how can i run this via hit of a button and will refresh data every hit. thanks a lot

  3. Dear Sir,
    I followed the above mentioned code to copy certain number of row which do I want to paste another work sheet. I have written the following code but it is not working. Can you please guide me where is wrong coding.

    Sub Car()
    Dim X
    X = 2
    Do While Cells(X, 2) “”
    If Cells(X, 2) = “Hero” Then
    erow = Sheet3.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“Sheet3”).Row(erow)
    End If
    X = X + 1
    End Sub

    1. Just replace 6th last statement and write

      Activesheet.Past Destination:=Worksheet(“Sheet3”).Row(erow,1)

      Now it will work

      Thanks Dinesh Sir for giving us an opportunity to learn VBA .

      1. Hi Amalendu

        I applied the solution mentioned by you
        but it is not working

        My excel version is 2013

        1. Hi, Can u sent the coding so that i can help you out, but in my office excel version 2013 it’s working. can u share the codding then only I can suggest you, where it is wrong.


  4. Hi Dinesh

    I am using the code below to copy and paste data from multiple worksheets into a master file
    It works just fine except it only copies over the values of the cells rather than all the formats i.e. cell pattern/colour, can you help?

    Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    Dim erow

    MyFolder = “C:\Users\Zoe\Desktop\ZAFiles2 – Working Copies1 – VJC”
    MyFile = Dir(MyFolder & “\*.xlsx”)

    Do While MyFile “”

    Workbooks.Open Filename:=MyFolder & “\” & MyFile

    Range(Selection, Selection.End(xlDown)).Select
    Application.DisplayAlerts = False

    erow = Range(“A” & Rows.Count).End(xlUp).Offset(1).Select
    Application.CutCopyMode = False
    MyFile = Dir
    End Sub

  5. Hi I want to copy the entire row based on criteria , cell value start “C” or “E” and from letf and secound criteria in Another cell value is “C” or “D” then copy the entire row on paste on next visible row
    and applyed this code but is not working plz help

  6. Hi. I want to do a search of multiple values and paste it on the new worksheet.


    Instead of just Car, I want to search for Ship, Boat, or even more than 1 value (Ship, Boat or Ship, Car or Car, Boat ) with a comma (,) in between.

    How do search for part of the word and not the exact word?

  7. I have a workbook with information tracking profit loss. The worksheet has 15 columns and the data starts in A7. There are column headers. I need to copy and paste all of the information to a new spreadsheet for reporting. I’m trying to write a macro so the report will run and take all of the information listed and put into a new workbook.

    The issue that I may run into is the entries are tracked by booking numbers. The numbers can be listed numberous time if there are multiple errors in a booking so there will be duplicates. But they run these reports weekly and I don’t want the data to be exported multiple times.

    There are separate tabs for each month. I would like it to stay that way.

  8. hi sir
    This code (in video link) is not working – it is showing a ‘Run time error’ and saying “Object required”
    can you please kindly help & suggest. thanks

  9. Thanks for this, it was very useful.

    Can i ask how you would include paste special rather than just paste. Specifically im looking for values and number formats. I have tried to amend it but doesn’t seem to like anyway i try.


  10. Thank you for some very interesting and informative videos.

    Unfortunately, when I cut and paste the above code into an Excel 2016 Module in an effort to duplicate your video training, I get an “out of range” error at this line:


    I have not been able to figure out a solution. Can you help with this.

  11. Hello,
    Your videos are very helpful! thanks for taking the time.
    I am trying to write a similar code, however I’d like the “search criteria” to be a variable in sheet2.

    using your example above, I will push the headers to row2, and will have an empty cell A1 in sheet2 and a button with command. when inputting the word “car” into A1 in sheet2, it will go to Sheet1, search for entries in column B in Sheet1, copy the whole row, and paste it to the next available row in Sheet2.
    Then, if I go back to Sheet2, and type in the word “Seats” in cell A1, it will replace the rows with the new search results, meaning, the full rows from Sheet1.
    most importantly, I just want to use this as a “filter” so I do not want the information in Sheet1 to be deleted at any point.
    is that possible?
    would you please assist?

  12. Hi
    the code posted below is working fine. Except the code in
    Sub SaveAsString()

    The last file created is not closing. any help would be thankful.

    Option Explicit
    Sub SaveAsString()
    Dim i As Integer
    Dim lRow As Integer
    Dim sPath As String
    Dim sFileName As String
    Dim wb As Workbook
    sPath = ThisWorkbook.Path
    lRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    For i = 2 To lRow

    Range(“A” & i & “:” & “M” & i).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    sFileName = Range(“N” & i).Value
    ActiveWorkbook.SaveAs filename:=sPath & “\” & sFileName & “.xlsx”, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Range(“A” & i & “:” & “M” & lRow).Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    Next i
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

    Sub DeleteColN()
    Dim folderPath As String
    Dim filename As String
    Dim wb As Workbook

    Application.DisplayAlerts = False

    folderPath = ActiveWorkbook.Path & “\” ‘change to suit
    If Right(folderPath, 1) “\” Then folderPath = folderPath + “\”
    filename = Dir(folderPath & “*.xlsx”)
    Do While filename “”
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(folderPath & filename)
    ‘Call a subroutine here to operate on the just-opened workbook
    If filename = “zmaster.xlsm” Then
    Exit Sub
    Call DeleteColumnN
    End If
    filename = Dir
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

    Sub DeleteColumnN()
    Selection.Delete Shift:=xlToLeft
    End Sub

  13. Hello Sir,

    Good Day,

    I was using the exactly VBA as given in the example and i have been hit with an error as below line;


    Run Time Error ‘9’

    Please let me know where is the mistake or this need to be change.


  14. Hello Sir,
    Your site is very helpful for me, a beginner in VBA . I just finished a user-form to enter data of a census into a spreadsheet. I´m very satisfied with the product I have obtained by looking your lessons. I want to be able to copy a range of cells A to D of the last row containing data and paste it in the next empty row. It has been very tough for me to get the correct code. My MS Excel version is 2010. Do you have any video containing that specific code? Thanks for your help.

  15. Sub copyDataFromMultipleWorkbooksIntoMaster()

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

    FolderPath = “D:\Checklist_Data\”

    Filepath = FolderPath & “*.xls*”

    Filename = Dir(Filepath)

    Dim lastrow As Long, lastcolumn As Long

    Do While Filename “”
    Workbooks.Open (FolderPath & Filename)

    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

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination = Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 23))

    Filename = Dir

    Application.DisplayAlerts = True
    End Sub

    1. i got an error 13 type doesn’t match kindly help me

      the highlighted command/script is

      ActiveSheet.Paste Destination = Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 23))

      Please help me

  16. I want to copy one row from sheet2 to sheet1(but I don’t want to copy all the data into one row)
    It is a data form that has 55 rows.
    After copy data for the first time, i need offset sheet(1) for 55, and sheet(2)for one row.
    This is what I need
    first time copy:
    Sheets(1)(“J6”) = Sheets(2)(“G7”)
    Sheets(1)(“C8”) = Sheets(2)(“H7”)
    second time
    Sheets(1)(“J61”) = Sheets(2)(“G8”)
    Sheets(1)(“C63”) = Sheets(2)(“H8”)
    third time
    Sheets(1)(“J116”) = Sheets(2)(“G9”)
    Sheets(1)(“C118”) = Sheets(2)(“H9”)
    and so for example 100 times.

Comments are closed.