1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

Discussion in 'Business Applications' started by GamerQQ, Jul 29, 2014.

Thread Status:
Not open for further replies.
  1. GamerQQ

    GamerQQ Thread Starter

    Joined:
    Jul 29, 2014
    Messages:
    2
    Hello my fellow databasers.

    The Information:
    I am currently working for a company that is tracking their part(s)
    expenses per each customer job.

    All customers' expenses are entered into the same 'primary' workbook so entering information is easier than going to each customer workbook. The column headings are as follows.

    Customer Name - Date - Part Used - Cost - Invoice # - Additional Notes

    There is 1 primary workbook and then each customer has their own workbook.

    I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for. I believe a macro is what I want though.


    The question:
    How, or what would I do, to have each workbook search for its relative 'Customer Name' and paste (auto populate) the information into the workbooks?

    ex. I entered a part for John Doe within Primary Workbook. The second workbook sees John Doe, copies the row of all parts that have John Doe in the first column and populates it in the second workbook.

    Also, would I need to create a separate Vlookup first to add a value to my customers so it wasn't text based to help the above equation work?


    I know I could copy and paste each one, but sometimes we retroactively change values of costs, and for the amount of time I could save by creating ... a macro? or some formula would be well worth continued research. Any help is appreciated.

    Application:

    It's important for my employer to be able to see what parts were used on each job when settling up with clients. It will make his life and mine easier if I can get this function to work. Otherwise we are looking through hundreds of lines of parts, using the 'find' command.

    Thank you for all help and comments.
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi


    A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.


    Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
     
  3. GamerQQ

    GamerQQ Thread Starter

    Joined:
    Jul 29, 2014
    Messages:
    2
    Xcubed,

    Probably the best response anyone could have given.

    1. Filtering eliminates sifting through lines of parts. It does essentially what I want and gives me the ability to copy and paste parts info into the other workbook on the fly. I like this because I can filter based on different criteria.

    2. Access. I was thinking, this is going to be awful but in reality. I opened it up, created my headers and realized that it allows you to put data from any table in a much cleaner less complex ( for an end user) way.

    Problem solved, I will be using the filter to appease my boss' need to see data at the end of the day and over the course of the next few weeks I will transfer all the data from Excel to Access.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1130580

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice