Transfer Data from one Workbook to another Workbook

  • [FONT="Times New Roman"]I would really appreciate some help with this one. Ihave spent many hours on this with no results.[/FONT]
    [FONT="Times New Roman"] [/FONT]
    [FONT="Times New Roman"]I would like to be able to transfer specific data fromthe Quote Workbook to the Reports Workbook.[/FONT]
    [FONT="Times New Roman"] [/FONT]
    [FONT="Times New Roman"]In the Reports Workbook I have a sheet for each salesstaff member and a Master List. When transferring the data from the Quote, I wouldlike to send it to the sales staff (H9) selected in the quote sheet to theirsheet in the Reports Workbook as well as to the Master List. I hope this makes sense. [/FONT]
    [FONT="Times New Roman"] [/FONT]
    [FONT="Times New Roman"]The data from Quote Workbook cells H9, H8, C6, H6, andI41.[/FONT]
    [FONT="Times New Roman"]Transfer to Reports Workbook starting in cells B5:F5 inthe selected sales staff’s sheet and Master List.[/FONT]
    [FONT="Times New Roman"] [/FONT]
    [FONT="Times New Roman"]I need it to record every time a quote gets made. [/FONT]
    [FONT="Times New Roman"] [/FONT]
    [FONT="Times New Roman"]I have attached a sample of each workbook.


    ozgrid.com/forum/core/index.php?attachment/64654/[/FONT]

  • Re: Transfer Data from one Workbook to another Workbook


    The Cells that you ask to be copied over do not match the columns B:F


    H9 is Staff Name Column B is Name
    H8 is Quote date column C is date


    so those correct


    C6 is Customer column D is Type of Quote
    H6 is type of Quote column E is Quote Value
    141 is Quote Total column F is Date Sold


    These 3 do not match.


    Either you are missing a column in you SampleReport Book, or do you not want Customer copied over from the quote?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    If you do not want Customer Name ("Prepared For") transferred then use:



    If there should be a column between Date and Type for Customer then use:


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    Thank you so much. You are right about the Customer Name. I missed it in the sample reports.


    When I run the code it gives me a run-time error and highlights the line "With Workbooks("SampleReports") '// Change as required". I did change the workbook name to the right name for my book. It is in the same folder the Quote one is.

  • Re: Transfer Data from one Workbook to another Workbook


    Is the Report workbook open? If not you will either need to open it before running the code or add code to open it

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    I have tried it with the Report workbook open and it still comes back with run-time error. The Report workbook is password protected with VBA so I'm not sure how to run a code to open it. When the sales staff open the Report workbook I only want them to be able to see their own sheet.

  • Re: Transfer Data from one Workbook to another Workbook


    This will open the Reports file, update it, then close it again saving the update.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    So each Staff member has their own password, and when the workbook opens code runs to limit access to just that users sheet?


    What is your code for unprotecting with password?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    It still has an run-time error and now highlights "Workbooks.Open "SampleReports.xlsx". The Report workbook is a .xlsm, changed that and still nothing. Sorry for making this so difficult.




    I used a tutorial from this site http://www.onlinepclearning.com/excel-userform-login/


    The full code in the workbook is...


  • Re: Transfer Data from one Workbook to another Workbook


    There is nothing in that code that unprotects the staff worksheet, it just determines which sheet is visible rather than hidden.


    Quote

    It still has an run-time error and now highlights "Workbooks.Open "SampleReports.xlsx". The Report workbook is a .xlsm, changed that and still nothing


    How can SampleReports.xlsx be highlighted if you have changed it?


    What is the actual name of your Report workbook?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    The actual name of the workbook is Reports. I just don't understand what I am doing wrong.

  • Re: Transfer Data from one Workbook to another Workbook


    I changed the SampleReports.xlsx workbook to Reports.xlsm, hid all sheets except for Bev, then created a dummy quote for Cornie Peters, ran the code below, created another dummy quote for Cliff Sawatzly and ran the code again.


    Then opened Reports.xlsm to check and each Staff sheet and the Master sheet (all of which were hidden) updated correctly. All was correct (I added extra column to each sheet for Customer)



    This code is in the Quote template workbook.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Transfer Data from one Workbook to another Workbook


    I am at a complete lost here. I can not understand why it is not working for me. Could there be something in my original workbooks that is causing the problem?

  • Re: Transfer Data from one Workbook to another Workbook


    I found out that the code KjBox gave me does not work on Windows 8. When I took the project to work and tested it one more time, it worked perfectly. I took it home to continue working on it and it did not work again. One of my coworkers also has Windows 8 and the same error keeps coming up. Everyone that does not have Windows 8 can use the reports code.


    Anyone have an idea why it does not work on Windows 8?