Transfer Specific Data from one Worksheet to another for Reports

I would like to create a quick report from data in one worksheet by transferring relevant data to another worksheet. For example, I would like to transfer all data about projects in progress to another worksheet every time I open my workbook. Is it possible to achieve this using VBA?

So Josh would like to transfer specific data from one worksheet to another to create a quick report so that he can quickly determine all the tasks or projects that have been completed and the tasks that need to be done.

The complete VBA code for the transfer of data from Sheet1 to Sheet2 is given below:
Private Sub Workbook_Open()
Dim i, LastRow

LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “E”).Value = “” Then
Sheets(“Sheet1”).Cells(i, “E”).EntireRow.Copy Destination:=Sheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

After the data is transferred to Sheet2 we can clean up the first sheet (Sheet1) using the following VBA code:
Sub deleteRowsTransferred()
Dim i, LastRow
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “E”).Value = “” Then
Sheets(“Sheet1”).Cells(i, “E”).EntireRow.Delete
End If
Next i
End Sub

In this manner we have create two reports quickly – one having the tasks in progress and the other having the completed tasks.
Watch the training video below:

Further reading:
VBA loops

48 thoughts on “Transfer Specific Data from one Worksheet to another for Reports

  1. HI,

    I am currently trying to create a Macro that will copy data from selected cells B2:H24 from Workbook1 and paste them into a different, unopened workbook called End result in the next free row into column J.
    I also need the information from columns B-H In workbook1 to be copied and pasted into cells A-G in the Workbook endresult and for this to be filtered down to all cells that have just had information pasted into them.
    Then I need to save the changes in the endresult Workbook and close it. I would also like the information taht was previously in workbook1 to be deleted.

    I can get different elements of the process to work through different marcos but it wont work when I try to put it all together.
    even if you can guide me to a previous clip that shows this information that would be greatly appreociate.

    1. This video is perfect thank you so much, but instead to clearing the sheet how would you just add it to the last row so I can have the tab updated every time i run the code? thank you again for everything.

  2. I have a sort of opposite question.
    I want to copy just the rows from sheet1 in a workbook “workbook1” that are selected in a different workbook “selection workbook1” [or2, or3] sheet1, (these rows may change per “selection workbook”) to sheet1 in another workbook “User1 workbook”.

    Workbook1 may have 3000 rows, “selection workbook1” could have the same number of rows, or just the selected rows that are required in “User1 workbook”.

    About 500 to 1000 of the possible 3000 rows will be selected to be included in “User1 workbook”

    I hope that I have added sufficent detail, if not I can provide more.

  3. The code for deleting would not work if there are to consecutive rows satisfaying the criteria since the Next i would jump to a row futher away from the row we must analize, please check this, best regards
    I love your videos and web site, very explicative

  4. Hi Dinesh,
    I am a beginner and i have seen your above video and found it very helpful. I tried to copy the data from one sheet1 to Sheet2 using VBA and it worked fine. however, when i tried to delete the copied data from sheet 1 it did not work. please let me know how to make both codes work together.


  6. hi , i am trying to create a sheet for work ( international haulage)

    have set the first sheet up fine it displays:
    date , haulier , trailer no. collection , delivery , etc… then it has milage on it that the driver has done on that job ,

    then i have sheets 2-8 for each haulier that i use , these sheets work out the rates for the milage (£1.35/£1.37 per mile) and drops (£15 each) , i then have it set to add the total cost of the milage and cost of the total number of drops to create a total for the hauliers work that week

    anyway down to the point , i would like to make it so all the milage for “jagdev”(one haulier) gets coppied from the traffic sheet (sheet1) onto his rates sheet (sheet2) , the same as all the other hauliers.

    is there a way to set it so it copies the correct milage for each haulier off the traffic sheet onto the rates sheets for each specific haulier ??

    if this didnt make sense i can explain it more clearly

      1. This video is perfect thank you so much, but instead to clearing the sheet how would you just add it to the last row so I can have the tab updated every time i run the code? thank you again for everything.

  7. I saw your Video “Transfer data from one Excel worksheet to another automatically” i used to work on that in that Video Only shows for single Line but i need to move two or more line to another sheet..

    Private Sub CommandButton1_Click()
    Dim JobDate As Date, OperatorName As String, ProjectName As String, StartTime As Date, EndTime As Date, TotalJobHours As Date, BStartTime As Date, BEndTime As Date, TotalBreakHours As Date, Title As String, IDStartTime As Date, IDEndTime As Date, TotalIdleHours As Date, TotalShiftHours As Date
    JobDate = Range(“A4”)
    OperatorName = Range(“B4”)
    ProjectName = Range(“C4”)
    StartTime = Range(“D4”)
    EndTime = Range(“E4”)
    TotalJobsHours = Range(“F4”)
    BStartTime = Range(“G4”)
    BEndTime = Range(“H4”)
    TotalBreakHours = Range(“I4”)
    Title = Range(“J4”)
    IDStartTime = Range(“K4”)
    IDEndTime = Range(“L4”)
    TotalIdleHours = Range(“M4”)
    TotalShiftHours = Range(“N4”)
    If Worksheets(“Sheet4”).Range(“A3”).Offset(1, 0) “” Then
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = JobDate
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = OperatorName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = ProjectName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = StartTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = EndTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TotalJobsHours
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = BStartTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = BEndTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TotalBreakHours
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Title
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = IDStartTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = IDEndTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TotalIdleHours
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = TotalShiftHours
    End Sub

    here by its my code i need to copy & Move the Content from one sheet to another Sheet..

    For Example “A4” to “A50” likewise “N4” to “N50”

  8. Hi,
    I’m very new to VBA. Came across your information when looking at ways to bring unfinished jobs from previous month to current month. And I thought the VBA process was an excellent method to achieve this.

    I’ve struck an obstacle, however. I’ve copied and pasted the complete VBA code but when I click on the arrow it comes up with a Syntax error. on the second line. (“A2:I500”).

    If you can provide any assistance it would be much appreciated.

    1. I am having the exact same problem. I am using excel 2007 what are you working with? Have your received an answer or have found out how to if it?

  9. Dear Sir,
    I am a beginner and i have seen your above video(Transfer Specific Data from one Worksheet to another for Reports) and found it very helpful. I tried to copy the data from one sheet1 to Sheet2 using VBA
    but some error massage occur, Pls help me.

  10. Hi Dinesh,

    Thanks for the video. This is really helpful.

    I am wondering is you can teach us how to NOT clear the content and instead paste or insert the copied rows within the similar conditions?

    PS> can we have the copied rows pasted or inserted by shifting the top row down?

    Thanks in advance.

  11. Hi All,

    I will be very thankfull if anyone can give me VBA code to copy selected data from sheet 1 to sheet 2
    I have done this by recording macros but i am getting stuck at if i select another data and run the macros it is replacing the first copied data in sheet2 it is not going to next row, so can anybody help me with this

    This wouldsave a lot of time

  12. I have a query, I have data with company names, address and company profits in sheets 1, I have to paste company name and profits in sheets two in acceding manner with highest profit company name highlighted with red color. how to do?

  13. I am having the same issue as Paul how commented on 13 Feb 2016 with this code
    It is not happy with “Sheets(Sheet2′).Range”A2:I500″).ClearContents”

    Private Sub Workbook_Open()
    Dim i, LastRow

    LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Sheets(“Sheet1”).Cells(i, “E”).Value = “Yes” Then
    Sheets(“Sheet1”).Cells(i, “E”).EntireRow.Copy Destination:=Sheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
    End If
    Next i
    End Sub

  14. dear sir,
    i need help to run macro,i have two workbooks in folder, first workbook having different sheets(sheet1,2,3..) these sheets having different columns in these columns on column is having CELLID with this CELLID i need to vlookup with another workbook which is having area and zone,my question is to open a new workbook which is having first workbook all data and with first wokbook data with sheets and vlookup with area and zone data with another workbook, and first workbook data is different each day but first workbook CELLID data (area & zone) are available in another second workbook,please check.


  15. Hi Dinesh
    I was following another tutorial of yours regarding copying data to another worksheet, however It does not seem to work, VBA shows an error message of
    Method ‘Paste’ of object ‘_Worksheet’ failed.

    The code is below.

    Sub copycolumns()
    Dim lastrow As Long, erow As Long

    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow
    Sheet1.Cells(i, 1).Copy
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheet1.Paste Destination = Worksheets(“Sheet2”).Cells(erow, 1)

    Sheet1.Cells(i, 3).Copy
    Sheet1.Paste Destination = Worksheets(“Sheet2”).Cells(erow, 2)

    Sheet1.Cells(i, 6).Copy
    Sheet1.Paste Destination = Worksheets(“Sheet2”).Cells(erow, 3)

    Next i

    applpication.CutCopyMode = False

    End Sub

  16. Hi Dinesh, i need help on the below:

    Scenario: I am currently building an excel template for my office. I have created a VBA-enabled TRANSFER button that transfers data from the template to a master workbook on a daily basis when clicked. There is a RESET button that clears out the data on the template when clicked on a daily basis. This is a control issue as someone might mistakenly click the button and all data would be lost.

    Question: Is it possible to get the RESET button to only be enabled when the TRANSFER button has been clicked? Please help me with a possible code for this.

    1. Do you need to have both buttons? I’m not the person you asked, but it seems to me that you could just have 1 button, that completes the transfer AND resets the sheet. That way, when the button is clicked, it completes the transfer and resets the page in the same code. It seems like that would be a great control so that you never risk clearing the page without transferring the data.

    2. I did find a solution for you, if you really want to have the two buttons:
      I think you’ll need two different lines of code. Insert this one as the last item on your “Reset Sheet” code:

      Sheets(“SheetName”).ButtonName.Enabled = False
      (Where ButtonName is the name of the Reset Button. This line will disable the reset button after it completes the reset each time. You’ll want to do this, to ensure that it only becomes enabled after the Transfer Button is pushed, and no one can clear the data in between transfers)

      Now insert this one as the last line for your Transfer Button Code:
      Sheets(“SheetName”).ButtonName.Enabled = True
      (ButtonName is still the name of the Reset Button. This ensures that the Reset button is enabled after the data is transferred)

      Hope this helps! 😀

    3. I am currently looking to do the same thing with transferring data everyday to a master workbook. Would you be able to share you code for the transfer?

  17. Hi

    I need a Vba code for copying data from one workbook to another workbook (master data) .Please provide it for me.Thank you in advance

  18. Hi all,
    Long time since I played with VBA, now trying to write code to copy order requirements from multiple worksheets to a master if someone enters a value in the Order_Qty column.
    Have tried several different ways and it falls over!

    1st sheet in workbook is Order_Sheet. Currently blank from row 11 down.
    Only 4 columns in use at the moment, Item_Number, Descriptio, RRP and Order_Qty.

    2nd sheet (Group_A), same titles in A1:D1
    15 Items listed, with description and RRP.
    Customer to enter Qty’s required in Column D.

    3rd sheet (Group_B) as per Group_A.

    4th sheet (Group_C) as per Group_A, but only 10 items.

    Any Help Appreciated.

  19. How can I write a code for quantity in between two values or range?
    I need a code for this particular example : 200<500


    Private Sub Workbook_Open()
    Dim i, Lrow

    Lrow = Sheets(“ORDER”).Range(“A” & Rows.Count).End(xlUp).Row

    For i = 3 To Lrow

    If Sheets(“ORDER”).Cells(i, “K”).Value = “COMPLETE” Then
    Sheets(“ORDER”).Cells(i, “K”).EntireRow.Copy Destination:=Sheets(“COMPLETED”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
    Sheets(“ORDER”).Cells(i, “K”).EntireRow.Delete
    End If
    Next i

    End Sub

  21. I have reviewed several of the post on transferring and saving data in excel and tried so of the macros that have been suggested. A couple work in which the data is transferred from the input workbook to another workbook but this is not what I am trying to do. I am trying to get a macro or something where the user enters data once and on worksheet it creates an invoice for the Finance section and it creates worksheet that is a running total of the data entered. The user inputs date of run and number of records processed and it performs a worksheet that the users can send to Finance also one that keeps the data they enter each month. This is the tricky part.

  22. Hi,

    I am trying to transfer data from the rows on one sheet to specific cells on another sheet. Sheet 1 has a header, and each column has data. For instance it would look similar to this:
    Name Fee Annual Renewal AL AK AZ AR CA…
    Bob Smith $400 $350 0 1 0 0 1

    I need to move that data into my sheet 2, but into specific cells. The name A2 needs to be transferred to CD6 (the cells are merged) on the other sheet. The fee from B2 should go to should go to CD7. The renewal on C2 should go to CD8. The other data is a little trickier. I need to bring the “AL” column on D2 to D12, E2 to D13, F2 to D14, E2 to I12, CA to I13. Is there a way to write a code that I can do this?

    I hope I am being clear.

  23. I have no previous experience with VBA, however when setting this code on sheet it worked perfectly for an individual sheet.
    I have 12 monthly sheets which I would require to obtain data from and display in report sheet. Is it possible to amend code to obtain the selected data from 12 individual monthly sheets and copy to report sheet?

  24. Please help:

    Can I control where the Macros are saved/recorded, such as sheet C ?

    Using a spreadsheet with 3 sheets, A, B & C
    I need one macro ctrl x to copy data from like B2 and C2 to where the active cell (such as K10 and L10)
    Then I move to a new K location (such as K22) and do ctrl x and copy B2 and C2 to K22 and L22.
    So I need the destination to be variable to current position.

    The other Macro (ctrl m) Say from COLUMN A, ROW xx, I need to move the current ROW on sheet A
    (to an established/PRESET row in sheet B). Then go back to sheet B and move down 1 ROW, then
    move back to sheet A. and delete the blank/or just moved ROW. At that point I would move to
    different ROW and repeat the process, With the Info on sheet B moving down 1 Row each time.
    So I need a variable in my Macro, so I Paste into current selected ROW instead of a fixed row.

    Thank you
    Ed Thompson

  25. I am trying to gather several worksheets into one master worksheet. I am planning to add a button for copying each worksheet so I need to identify where both worksheets end! This code worked only once and then stopped working, it stops right in this line: linea2 = ws2.Range(“A” & Rows.Count).End(x1Up).Row, with Run Time Error 1004!!!. What am I doing wrong??? Im a newbie and need some help

    Sub Button6_Click()

    Dim linea1 As Long
    Dim linea2 As Long
    Dim ws1 As Worksheet
    Set ws1 = Sheets(“1”)
    Dim a As Integer
    Set ws2 = Sheets(“Juntar”)

    linea1 = ws1.Range(“A” & Rows.Count).End(xlUp).Row
    linea2 = ws2.Range(“A” & Rows.Count).End(x1Up).Row

    For a = 2 To linea1
    ws1.Range(“A” & a).Copy Destination:=ws2.Range(“A” & linea2 + 1)
    ws1.Range(“B” & a).Copy Destination:=ws2.Range(“B” & linea2 + 1)
    ws1.Range(“C” & a).Copy Destination:=ws2.Range(“C” & linea2 + 1)
    linea2 = linea2 + 1

    Next a

    End Sub

  26. Hello,

    Thank you for your tips. I’m new to VBA and I am working on a school project using VBA. What you have posted is somewhat what I’m looking for but not quite. If you would please email me, at I would like to talk with you about what I’m trying to do and maybe you can help me with it? Thank you very much. Kobe

  27. Hi..I want to copy data (from sheet 1 range of any last used row) to (different cell of another sheet 2), I don’t want to clear the data from used rows of sheet 1 because my form read the starting row number of sheet 1 and then display it to form, if i clear the data from the last used row, it will display the earlier row number, which is not good for my project file. My querry is for how could the last used row will works as first row ? is there any code or filter ???

  28. Hi Dinesh,
    Thank you very much for your videos.

    I have s amall problem.

    In Sheet1 I have the following Data

    14123 Joe1 $1,234.00 29-Jun-17
    14124 Joe2 $1,235.00 29-Jun-17
    14125 Joe3 $1,236.00 29-Jun-17
    14126 Joe4 $1,237.00 29-Jun-17
    14127 Joe5 $1,238.00 29-Jun-17
    14128 Joe6 $1,239.00 29-Jun-17
    14129 Joe7 $1,240.00 29-Jun-17
    14130 Joe8 $1,241.00 29-Jun-17
    14131 Joe9 $1,242.00 29-Jun-17
    14132 Joe10 $1,243.00 29-Jun-17
    14133 Joe11 $1,244.00 29-Jun-17
    14134 Joe12 $1,245.00 29-Jun-17
    14135 Joe13 $1,246.00 29-Jun-17

    and in Sheet2


    Please help me with the coding of be able to copy the data from column2 and column3
    from sheet1 to sheet2 for common EID.

    thank you


  29. I have one master worksheet with a list of names on it. I want to make a code that takes that list of names and creates separate work sheets for each name and also auto populates cell D7 with that name.

    I am a newby to VBA

  30. Can use this program to transfer data that my data have formula and data validation when i use this program. it came up with an error. if its possible to use your vba code copy the output of the formula and data validation?

  31. Hi,

    I am trying to create an excel workbook that has a mastersheet about multiple programs and several sheets that have the same information as the mastersheet but only about one program. Is there a way to have the secondary sheets automatically populate as I enter the information on to the master sheet based on a specific value? So if in Sheet 1 cell B1 says “Program A” then row B is copied to Sheet 2 (which represents Program A) and if cell C1 says “Program B” then row C is copied to Sheet 3 (which represents Program B). Any help would be appreciated!

  32. Am trying to extact one particular data from the above given coding but it is showing error int he below


    Please help to learn why it is showing error

  33. Is it possible that i can transfer the whole row when the status was changed
    Ex. : from PENDING then DONE < when its done the whole row will transfer to other sheet same with other hundreds rows if i changed their status one by one they will transfer without duplication.

  34. Sub myledger()
    Dim lastRow As Long, i As Long, erow As Long
    lastRow = Sheets(“Day book purchase”).Range(“A” & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
    If Sheets(“Day book purchase”).Cells(i, 3) = “soda” Then
    Sheets(“day book purchase”).Range(Cells(i, 1), Cells(i, 6)).Select
    lastRow = Sheets(“soda”).Range(“A” & Rows.Count).End(xlUp).Row

    erow = lastRow + 1
    ActiveSheet.Cells(erow, 1).Select

    Application.CutCopyMode = False
    End If
    Sheets(“day book purchase”).Select
    Next i

    End Sub

    your add particualr values for “soda” mentioned rows entire records.
    “Create reports from raw data automatically with loops, auto-filter and VBA”

    in this above mentioned video your copied entire rows
    but i want also particular column. Answer this query immediately

  35. I am having difficulty with this code.
    I copied and pasted your code to my worksheet, used my specific naming conventions, but I keep getting the error code “Run-time error ‘9’” Subscript out of range. I have since learned that the variable I created, “LastRow”, is empty. No matter how I change the name of the variable, or the specific worksheet, the variable is always empty. Can you tell me what I’m doing wrong? I’ve pasted my code below.

    Private Sub Button9_Click()

    Dim i, LastRow

    LastRow = Worksheets(“Add_Task_Planning_Report_Here”).Range(“A” & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Sheets(“Add_Task_Planning_Report_Here”).Cells(i, “E”).Value = “0701 – JOINT_TREATMENT” Then
    Sheets(“Add_Task_Planning_Report_Here”).Cells(i, “E”).EntireRow.Copy Destination:=Sheets(“JT”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
    End If
    Next i
    End Sub

  36. Dr. Dinesh
    Dear Sir, It will be highly appreciated if you would kindly help with following:
    I have three workbook. Workbook-1 Master Data File. Workbook-2 Data FORM-1 and Workbook-3 Data FORM-2. Data FORM 1 and 2 send data to Master Data File using your following code.
    When FORM-1 send data to Master Data file its occupied example cell A2 to F2 when data from FORM-2 arrived its insert one row down that is in G3 instead of G2, I wants data to be inserted next available column columns which is G2. How to achieve that within below code . Also I want to close Master Data File automatically when Data FORM closed. Thanks Shaun

    Following code using in Data FORM-2 also same code use in Data FORM-1.

    Private Sub CommandButton1_Click()

    Dim quotationNo As String
    Dim myData As Workbook

    ‘Worksheets(“Sales Invoice 1”).Select
    quotationNo = Range(“K6”)
    ‘Worksheets(“Sales Invoice 1”).Select – this line may not be necessary

    Set myData = Workbooks.Open(“F:\EXcel\CustData.xlsm”)
    RowCount = Worksheets(“sheet2”).Range(“A1”).CurrentRegion.Rows.Count
    With Worksheets(“Sheet2”).Range(“A1”)
    .Offset(RowCount, 6) = quotationNo
    End With

    End Sub

  37. I like your video and example but not sure if you can help me because im trying to create a self grading quiz in excel. Example sheet 1 will be the exam and answers, at the end of the exam it will show the score (percentage) and then perhaps sheet 2 will be the results. Im trying to make it as simple as possible. On the exam page i would like a button to say “next” then the next question will appear. thanks

  38. Dear All,

    I was wondering how to create a VBA to copy & paste a dataset onto a new sheet. Criteria: are it has to carry over the information[COLUMN A TO B] base on the issues listed in the column. As the result the each sheet will have a column of ONE issue listed (see Results/Goal section)

    Row 1 is just references insuring ROWs 3 & on…. are matching

    Row 2 is just references and SUM of each column starting row4 & downwards. If the sum is greater than 0 then that issue needs to be transferred.

    Row 3 & below… are the dataset we want to copy & paste.

    2 Objectives for below:

    1) CRITERIA is if Row of that column SUMs is greater than 0 (ans. in row 2). Only then you want to COPY & Paste the details and that listed issue therefore Copy & Paste values only and move it to new sheet & ONLY carry over one issue at a time (starting from Issue 1 and onwards(rightwards)) & rename the sheet as “Issue1” (base on the column header row 1 or 3)

    2) Filter Z to A the new sheet across headers base on Issue1 column…. & so on.

    Note: {So there are three issues listed for this one example but it can go up to 14 issues [meaning 14 columns].}

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.