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

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
Sheets(“Sheet2”).Range(“A2:I500”).ClearContents
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

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

  1. Amanda

    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.

    Reply
    1. Brian

      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.

      Reply
  2. Neilg

    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.

    Reply
  3. RafaelParejaEmiliani (@RafaParejaE)

    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

    Reply
  4. Shruti

    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.

    Reply
  5. hARISH

    I HAVE MANY RECORDS IN A SHEET. I WANTS TO CREATE NEW EXCEL SHEETS WITH FILTERED DATA NAME. THE NEW EXCEL SHEET NAME SHOULD BE SAME AS FILTERED DATA. CAN ANY ONE HELP ME……

    Reply
  6. cj

    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

    Reply
      1. Brian

        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.

        Reply
  7. srinivasn

    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
    Worksheets(“Sheet3”).Select
    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”)
    Worksheets(“Sheet4”).Select
    Worksheets(“Sheet4”).Range(“A3”).Select
    If Worksheets(“Sheet4”).Range(“A3”).Offset(1, 0) “” Then
    Worksheets(“Sheet4”).Range(“A3”).End(xlDown).Select
    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
    Worksheets(“Sheet3”).Select
    Worksheets(“Sheet3”).Range(“A4”).Select
    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”

    Reply
  8. Paul

    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.

    Reply
    1. tash

      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?

      Reply
  9. Indika De Silva

    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.

    Reply
  10. Xune

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

    Reply
  11. Naresh

    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

    Reply
  12. sanober

    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?

    Reply
  13. tash

    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
    Sheets(“Sheet2”).Range(“A2:I500”).ClearContents
    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

    Reply
  14. irfan

    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.

    thanks.

    Reply
  15. Paul

    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
    Sheet2.Columns.AutoFit
    Range(“A1”).Select

    End Sub

    Reply
  16. Phil

    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.

    Reply
    1. shootergal89

      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.

      Reply
    2. shootergal89

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

      Reply
  17. supriya

    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

    Reply
  18. Spencer T

    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.

    Reply
  19. Liz

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

    Reply
  20. KHADIMHUSEN

    PLEASE HELP ME
    WHAT IS WRONG IN BELOW CODE IT IS NOT WORKING PROPER, IT MOVE ONLY HALF ROWS. IF THERE IS 6 COMPLETE ROW IT MOVE ONLY 3 ROW TO COMPLETED SHEETS.

    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

    Reply
  21. Shelia

    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.

    Reply
  22. Kayti

    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.

    Reply
  23. John

    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?

    Reply

Leave a Reply

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