+ Reply to Thread
Results 1 to 13 of 13

Macro to transfer data from main sheet to sub sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    09-23-2009
    Location
    liverpool, england
    MS-Off Ver
    Excel 2003
    Posts
    78

    Macro to transfer data from main sheet to sub sheets

    Hi

    I need some help with the attached workbook.

    I have macros on another sheet and workbook which puts the data in B12:15.

    Depending on the date b1 and the person specified b2, i want the data to then transfer over to the other sheets on the workbook.

    ie if gemma J did 25 pnc on 23-11-10, then i want it to take this over to the sheet gemma j and find the pnc box and input the figure for that date, in this case, b24.

    I then want whenever the next person clicks on a particular persons tab, that it immediately shows the last piece of data entered.


    I think this is quite easily possible, but am having problems with it. So if anybody could help i would be very appreciative.


    Kind regards

    Joe
    Attached Files Attached Files
    Last edited by sandbach; 11-02-2010 at 05:37 PM.

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Macro to transfer data from main sheet to sub sheets

    Joe,

    I was looking at your post and workbook, however I was unable to see what your talking about. If you could provide a before and after example.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Registered User
    Join Date
    09-23-2009
    Location
    liverpool, england
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to transfer data from main sheet to sub sheets

    Hello Charles,

    Thank you for your reply.

    I have updated the workbook with the changes and attached it. Sheet 1 contains the inputted data, and i would like it to automate it so it fills each employees records up with their totals from which i will generate averages.

    So on 23/11/2010 gemma j did 25 pnc 20 conflicts 17 isa and 12ids, and so i have manually updated the gemma J tab for the 23/11/2010 with her work totals, but i would like this process automated.

    I would like the macro to read the date b1 which will correspond to a row, the agent b2 which will correspond with a worksheet, and then the work totals b12:b15 which will correspond to columns b:e depending on the agent and the date, which row and which worksheet.

    i hope i have explained this better!!?

    kind regards

    Joe
    i hope i have explained this bettr
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Macro to transfer data from main sheet to sub sheets

    Joe,

    Yes it does thanks. If I do not get back too you on this today, maybe another forum member will also take a look also.

  5. #5
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Macro to transfer data from main sheet to sub sheets

    Joe,

    Here's what I came up with.
    You will see a command button. Make your selection and the click the button.
    Attached Files Attached Files
    Last edited by Charles; 10-30-2010 at 05:50 PM. Reason: info

  6. #6
    Registered User
    Join Date
    09-23-2009
    Location
    liverpool, england
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to transfer data from main sheet to sub sheets

    Hello Charles

    Thank you very much for that, unfortunately i cant get it to work, does it make a difference that cell b2 is a data validation list table with all agents names?


    kind regards

    Joe

  7. #7
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Macro to transfer data from main sheet to sub sheets

    Joe,

    It should not matter if it's from a dropdown the value in B2 will be that of the dropdown selection.
    Also what do you mean by you could not get it to work. What happened?
    Also I might suggest being you want to see the last entry to the Agent than instead of using the dates as you have then in the worksheet that you would when the button is clicked add the data to the next empty row in the Agents worksheet. This way you will see the last entry.

    In this attachment you will see button 6 you can onlt use gimma j for testing. I modifed the agents worksheet.
    Attached Files Attached Files
    Last edited by Charles; 10-31-2010 at 12:32 PM. Reason: added attachement and info

  8. #8
    Registered User
    Join Date
    09-23-2009
    Location
    liverpool, england
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Macro to transfer data from main sheet to sub sheets

    Thanks Charles,

    The second button works perfectly, i think that is a better way but i will have to modify my averages, but thank you so much for all your help, this will certainly make my life a lot easier.

    I still cant get the first button to work, not sure whats going on with that.

  9. #9
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Macro to transfer data from main sheet to sub sheets

    Joe,

    About ready to leave, but when you say you can not get it too work?
    Whats not working? Do you get errors?

+ 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