+ Reply to Thread
Results 1 to 9 of 9

Automatically transfer information from a Raw data sheet to Report sheets.

  1. #1
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    110

    Automatically transfer information from a Raw data sheet to Report sheets.

    Hi All,

    Each month I have to do a report for various clients for their sales of ringtones. At the moment, I take the information from the 'Raw Data' sheet and manually enter the information into the 'Report' and 'Sales Breakdown' sheets. What I would like to know is would I be able to automate the data in the 'Raw Data' sheet so that it automatically completes the 'Report' and 'Sales Breakdown' sheets.

    I attach a copy to hopefully explain what i mean.

    Cheers in advance.
    Attached Files Attached Files
    Last edited by NBVC; 05-01-2009 at 10:56 AM. Reason: Help to fix title

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with creating a report

    This macro will create the Sales Breakdown report. You haven't provided enough sample data to fully attempt the Report, apparently there is a whole section "T" type codes that have to be addressed, you've shown no example nor explained/demonstrated how to report them.
    Please Login or Register  to view this content.
    Also, I put some "rough match" code in the part to get the MCPS code, but there are variations in spelling, too, that you will have to repair in the data. Perhaps put in versions in the chart that have the alternate spellings...like "Fathers" and "Father's"

  3. #3
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Help with creating a report

    Thank you so very much for helping me with this. As you can probably tell with the sheet that I sent you, I am not overly proficient in Excel. How do I use the macro that you so kindly provided as I am not sure how or where to put it.

    Cheers.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Help with creating a report

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Automatically transfer information from a Raw data sheet to Report sheets.

    I changed title for you so you can see what kind of title we are looking for... this makes searching/finding subjects easier for others...when googling.

  6. #6
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Automatically transfer information from a Raw data sheet to Report sheets.

    Thank you for the Title Change, I wasn't sure how to word it.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatically transfer information from a Raw data sheet to Report sheets.

    1. Open up your workbook.
    2. Get into VB Editor (Press Alt+F11)
    3. Insert a new module (Insert > Module)
    4. Copy and Paste in your code (given above)
    5. Get out of VBA (Press Alt+Q)
    6. Save your sheet.
    Now press F8 and select MakeReports from the macro list.

    This macro will clear the contents of the existing Sales Breakdown sheet and replace it with the items on "Raw Data", then add the matching MCPS code as best it can.
    Last edited by JBeaucaire; 05-01-2009 at 11:19 AM.

  8. #8
    Forum Contributor
    Join Date
    04-25-2007
    Location
    Cardiff, South Wales
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Automatically transfer information from a Raw data sheet to Report sheets.

    That seems to work excellently! And I now see what you mean about repairing the data. Will it also work if the number of rows exceed the ones that were given in the example?

    Cheers.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatically transfer information from a Raw data sheet to Report sheets.

    No. Would you like the "totals" on the Sales Breakdown to be added by the macro? This adjusted code will handle that.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1