+ Reply to Thread
Results 1 to 93 of 93

Data transfer from one sheet to another multiple cells

  1. #1
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Data transfer from one sheet to another multiple cells

    Good day,
    I am trying to create an auto-generated log on a new sheet that is based off of data entered from a previous sheet. What I am wanting is when a single cell on the master sheet is modified (in this case a choice is made from a drop down list), a copy of certain cells in that row would be transferred to the new sheet. Now here's the kicker, I also want it to create a new entry every time that the cell is changed with the newest being at the top of the list. An example is Sheet1 has multiple rows with each column containing certain data

    -------A--------B----------C-------------D------------E
    1--- Name----ID#----Location----Time out-----Time In------ etc.
    2--- Smith----1234----Chapel-------9:30---------10:45
    3--- Jones----9543----Hospital------7:15---------11:30

    Column C contains the drop down list and auto-generates the time in Column D. What I want is when the location is changed in Column C I want it to copy all the required data from the row, but not the entire row, onto Sheet2. Does anyone have any ideas on how to accomplish this? Please help.

    Sheet 1 is Titled (Accountability)
    Sheet 2 is Titled (Auto-Log)
    Last edited by demetrius323; 11-16-2014 at 12:55 PM. Reason: formatting

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi demetrius,


    Welcome to the Forum!

    You could just keep inserting the new row at the top


    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Last edited by xladept; 11-16-2014 at 04:49 PM. Reason: Canned Reply
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Copy 1 is what the page looks like before any movement has been done. Notice that there are 2 sheets. The Location (Column D) has a drop down menu. Copy 2 is what I want the pages to look like. I have already formatted the first sheet with conditional formatting to show color change and added formulas to make all the visible calculations and tallies on the page. What I want is when Column D is altered for an inmate, lets say he went to Medical, then I would like that record to reflect on the second sheet of the Workbook to include all the pertinent information it asks for. When the pages are locked, only the Name#, Name, and Location are able to be manipulated. All other cells on the first sheet are locked and all the cells are locked on the second sheet. Any advice would be greatly beneficial. Thank you.
    Last edited by demetrius323; 11-21-2014 at 07:55 PM.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Thanks for the rep - I'll look at this again tomorrow

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Demetrius.xlsm - Try this

  6. #6
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    nothing is happening after i open it. it is still the same spreadsheet that i uploaded

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius - have you used macros before?

    If not you need to go to your options - trust center -trust center settings - macro settings, the second option down (disable all macro with notification).

    Then when you open the book you need to allow macros

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Demetrius.xlsm - Try this one instead
    Last edited by xladept; 11-18-2014 at 07:08 PM.

  9. #9
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    it worked great except for one problem. when I deleted the location, as if the inmate returned to the housing unit, it updated that he was signed out. is there any way to modify it so when the location is deleted the inmate is signed back in? other than that, it is amazing.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Glad you like it

    Replace the code called Demetrius in the Accountability sheet module with this:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    It worked great until I deleted the location out of one cell. Then it quit working all together unless I closed it and reopened it. Also I think I found a solution to the time in problem. I have uploaded the most recent version with the adjustment you made. I have added a "Location copy" column ( i plan on formatting the cell to copy the data in the location column when text is present)and a "time in" column on the Accountability sheet. In the final version I can make that text white so it is not seen. Now, instead of the data transfer occurring when the location is initially changed, can it be triggered when it is cleared? If so , I can do some conditional formatting to place the time in when the location shows blank. Do you think that this might work? Once again, thank you so much for your help.
    Last edited by demetrius323; 11-21-2014 at 07:56 PM.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Try replacing the code with the above again - I may have edited it after you copied it

  13. #13
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    awesome! now i just have to figure out how to link it to trigger for the time in, like i stated in my above post. other than that, you did a fantastic job. I would be lost on this project without you. Thanks. I know its asking for a lot but do you think that you can still provide some extra assistance?

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    I can help more - can you elaborate?

  15. #15
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Ok, ultimately I want this to be expanded to our entire jail for the officers. We already have enough paper work to deal with and everytime we send an inmate somewhere we have to search them, locate their name their number and cell location and hand write them out on a log. This spreadsheet can eliminate that paper paper work and save that much more time that the officer can spend remaining diligent in the housing unit. To locate someones name on the roster and click a location on where they went, and then clear the location when they get back is all I want the Officers to have to do. Most aren't very computer saavy and niether am I but we need a change, for our own protection. I am hoping that the macro you designed and be altered to trigger when the location is cleared. That way, if I can form the right cells, it will fill in the auto-log in chronological order with both a location and a time in and out. Does that make any sense? Do you think thus can be accomplished. And if so I will be saving different versions of the spreadsheet and adapting them to each housing location. the accountability sheet is the only sheet the Officer should need to touch, the other sheet would be for record purposes. Can we also accomplish this and lock the cells on the auto-log so they can't be tampered with?

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Sure, we can do that - but, I need to know the specifics on the time-in. I.E. do you want it automatic for when the location is erased????

  17. #17
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I have uploaded the most recent version with the changes that you made. As you can see on Sheet 1, I have placed 2 new columns that are not highlighted; Location Copy and Time In. These columns can eventually be used with white text so that they are not visible and the cells will be locked as well. What I would like is when the location is entered, a time out will be entered on Sheet 1. That can be done with standard conditional formatting. What is need is when the location is cleared it will place a Time In and transfer the data to Sheet 2. All while keeping the Time out, Time In and the location that the inmate was at intact. That is why I created the 2 new columns, as a place to put in information if it is required for the Macro. However, I realized, that if the Macro is not triggered until the inmate has returned, the time outs will not be in chronological order. It would be best to sort the entries based on the Time out in a Newest-on-top format. Are there any other specifics you may require? Oh, one more thing. On Sheet 2, when the Time Out column reaches a new year can it be transferred to a new sheet with the updated year? Can it automatically create an identical page or should they be pre-made and just waiting until that year comes? Can it be down to extend years down the road, like 5-10 years? Also, I have added an ATW and Loss option to the Location drop down list. Is is possible to upon setting the location as ATW or Loss it can transfer the data to Sheet 2 with the location, Time Out and ATW or Loss ( in place of the time in)? And at the same time clear the contents of that row on Sheet 1 with the exception of Assigned Housing and all the conditional formatting?

    Can we also placed a total time out next to the time in column on Sheet 2 as a quick reference on how long the inmate was out of the housing unit for each entry? Sorry, I know that I am asking a lot. My mind is going a million miles a minute. You have helped me so much with this and I greatly appreciate it, my whole department will thank you.
    Last edited by demetrius323; 11-21-2014 at 07:57 PM.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Here's the next version:


    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    That worked great for putting the Time In in place. A couple more questions and I think we have a winner. Is it possible when ATW or Loss is selected that the Time In would reflect the Value of ATW or Loss since these options mean that the inmate is not returning. And at the same time remove all of the data from that inmates row on the Accountabilty Sheet with the exception of the Assigned Housing and all of the Conditional Formatting so it is prepped for a new Inmate to be entered in when they arrive? Also, as of right now the Auto-Log Sheet is sorting the entries according to the Time In, I need it to sort for the Time out. Also, is it able to start a new sheet when the year changes? Can every sheet past Sheet ! be protected to prevent tampering? Other than that, it is working out fantastic.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    I'll work on it again tomorrow

  21. #21
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Ok. Thank you.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Try this:


    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    That's awesome! It created a new sheet called Auto-Logx, do I need that? can I hide it? Also, just a minor tweak, the data on the Time in for Loss and ATW. It put all of the inmate information in there. I just wanted the phrase "Loss" or "ATW" respectfully. I also need the password to unlock the sheets. Once those minor things are adjusted, we are all set!

    Oh, I just noticed, when I change the Location to "Other (specify)" it creates the log entry but when I place a specific location in the next cell over it creates a new log entry instead of merging, can we do that so only one entry exists?
    Last edited by demetrius323; 11-21-2014 at 05:34 PM. Reason: Update

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Auto-LogX is the old sheet, and, yes we can hide it. The password is the old word for Jail, it's "Gaol". I think I've
    fixed the "Other" problem (I had forgotten the space before the parentheses). I've made a few more improvements

    Please Login or Register  to view this content.

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Oops - it' not quite ready

  26. #26
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    The "Other " problem still exists. Here is the latest version with all your modifications. What will happen when the year changes to 2015 on so on? Will it separate onto a new Sheet or will it continue to post on the same one? I am hoping that years can be differentiated.
    Attached Files Attached Files
    Last edited by demetrius323; 11-21-2014 at 08:01 PM.

  27. #27
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Try it this way:

    Please Login or Register  to view this content.

  28. #28
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Still not working with the "Other (specify)" merge. I changed the date on my computer to see how it would react to a new year and it did create a new Sheet but it transferred some entries from the previous year, and I also ended up getting an error.

  29. #29
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    I'll work on it again tomorrow

    Please Login or Register  to view this content.
    And, in the event code insert a space between "Other" and "(specify)".
    Last edited by xladept; 11-22-2014 at 12:00 AM.

  30. #30
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    That definitely fixed the "Other (specify)" problem. What about the year issue? Is there a way for it to create a new Sheet every time a new year rolls around? If not, what suggestions might you have to help simplify the problem?

  31. #31
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Maybe?


    Please Login or Register  to view this content.
    Replace all the code in the Accountability sheet module with this and then let me know of any issues

  32. #32
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I think that works. I'll just have to advise everyone sign everyone back in at midnight and then re-sign them out. There is no one out of the housing unit at that time 99% of the time anyways. Now I just have a non-VBA related question. We have common computer drives that we all are connected to. I tried this before but I might be doing it wrong. Is there a way for it to auto save and be accessible as an up-to-the-minute "read only"? An example is if there is a fire drill, and the Officer forgot to hit save, can it be accessible from another workstation computer to print out so can do an accurate head count? When I tried to do it, it would only show me the way it looked when the user first opened it and not everything since then.

    Oops... found another problem. When I protect the Accountability Sheet, it will not work. I want the users to be only able to manipulate the Inmate Number, Name, Location and the blank cell next to Location. I don't want them to be able to mess with anything else.
    Last edited by demetrius323; 11-22-2014 at 05:44 PM. Reason: Update

  33. #33
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    I'll just have to advise everyone sign everyone back in at midnight and then re-sign them out.

    Why would you do that?


    We can save the Workbook with each change:

    Please Login or Register  to view this content.

  34. #34
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Quote Originally Posted by xladept View Post
    Why would you do that?
    If the year happens to change and there are people signed out, it did created a new log entry for them on the new year with the time out of the old year which is good I guess but it also let the original sign out on the previous year never signed in.

  35. #35
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Oh, and I would be kicking myself in the butt if I forgot this. On the Auto-Log, can we create 2 new columns for user id. What I'm saying is "Officer Out" and "Officer In" I may be asked to include something like this as many times an inmate is signed out by one Officer and signed back in by another (shift change, etc.) Can these columns be placed next to the Time Out and In respectfully and input the current computer user? This would be to help narrow down who to speak with if any discrepancies were to occur in the log. What ever User triggered the time out would be placed next to it and the same as the Time In as it may be a different Officer. Does that make any sense or should I try to explain it better?

  36. #36
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Should the year change, all of the open entries get transferred to the new sheet. Again, I'll work on your enhancements tomorrow

  37. #37
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    You are the best! I wish i could accomplish what you make seem so easy. I owe you big time.

  38. #38
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Here is the book that I used to build the code along with the code - Let me know of issuesDemetriusIV.xlsm

    Please Login or Register  to view this content.

  39. #39
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Something is wrong. For some reason it isn't doing anything.

  40. #40
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    It won't work until you put an entry in the Officer field and that entry must be last!


    I thought of another enhancement

    Please Login or Register  to view this content.
    Last edited by xladept; 11-23-2014 at 05:42 PM.

  41. #41
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Is there any way to auto enter the Officer? Each Officer has their own log on to the computer systems and so they also have their own User Name attached to Microsoft Office. Is there any way that the spreadsheet can pull that information and enter it into the cells for Officer Out and Officer IN on the Auto-Log? It's not necessary to have it on the Accountability Sheet. That just makes that much more that the Officer has to remember to do. Simple as possible for the Officer is my goal.

  42. #42
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Try this:


    Please Login or Register  to view this content.

  43. #43
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Just a small tweek. When the inmate goes ATW or Loss, can we fill in the Officer In slot on the Auto-Log with the one who inputted that data, in this case it would be the same as the one who is listed for Time Out.

    And also Protect the Accountability Sheet.
    Last edited by demetrius323; 11-23-2014 at 06:20 PM. Reason: Update

  44. #44
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Please Login or Register  to view this content.

  45. #45
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I might be doing something wrong. it's not working.

    I am getting an error code.
    Attached Files Attached Files
    Last edited by demetrius323; 11-23-2014 at 07:04 PM.

  46. #46
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Try this:


    Please Login or Register  to view this content.

    * You may need to run this first:

    Please Login or Register  to view this content.

  47. #47
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I didn't have to run that other part first. I think we have a winner! Thank you so much for all your help. How is it you came to be so knowledgeable in VBA. I'll mark this as Solved! How can I ever repay you? Would you like credit when I present it to my Chief?

  48. #48
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    You're welcome and thanks for the compliment!

    I've been doing this for a long time and not just on the Forum - although my code has improved tremendously because of the Forum. Since, I make my living this way - a word of mouth credit might help me going down the road

  49. #49
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    You got it! If i ever need help with vba again I'm going straight to you. You're awesome!

  50. #50
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Hello again. I know I marked this as solved but something was pointed out to me by a peer that should be an easy fix. As we have already made it so ATW and Loss generate time in and out simultaneously, as well as putting the respected term in the time out slot, can we do the reverse for the term "Gain"? What I mean is place the term "Gain " in the Time Out slot and the time of the occurrence in the Time In slot. But instead of clearing all the data like ATW and loss, just automatically clear the location. This is because every time a location change happens, an auto save occurs, unlike if I just type some one in the Name cell I have to physically hit the save button, which can be forgotten, and also it will automatically update the count in the housing unit. Also this will generate a log showing that someone was gained to the housing unit.
    Last edited by demetrius323; 11-25-2014 at 07:36 AM.

  51. #51
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,


    See if this is what you wanted:

    Please Login or Register  to view this content.
    Last edited by xladept; 11-25-2014 at 02:56 PM.

  52. #52
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    That's the right idea. for some reason it did not put the User ID in the Officer In slot. Also the time and officer were not clearing off of the Accountability Sheet when "Gain", "Loss" or "ATW" were used.

  53. #53
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,


    See if this is what you wanted:

    Please Login or Register  to view this content.

  54. #54
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Now it is not putting the User in the Officer Out slot. It is also clearing all the data on the Accountability. I just want it to clear the Location, Time Out and Officer Out slots.

  55. #55
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Please Login or Register  to view this content.
    ??

  56. #56
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Bingo! There it is! By the way, I presented it to one of my Captains today and she said that she loved it. We are going to do a trial run here soon to see how it works out. If everything goes well it will be implemented soon. Thanks again.

  57. #57
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    You're welcome! Glad to hear it

  58. #58
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Thank you again for your help a while back. We are beginning our trial run tomorrow. I had a couple of issues brought up by my Captains that I'm sure are an easy fix. In both of the attached documents is it possible to:

    1. protect all the sheets from being deleted. This includes the ones that become hidden when a new year arrives.

    2. disable developer mode when the sheets are locked. This will keep anyone from tampering with your coding.

    In the sheet labeled "Medical Out Counts", it is currently formatted with your original coding but needs to be modified. The drop down box has been changed to the column for "Assigned Housing" and now triggers everything. Before when the drop down box was cleared it would clear all information except for name number, inmate name and assigned housing. Now I would like it to clear all the information including those. Just like how the information was cleared when "ATW" or "Loss" was entered is how I want it to affect the data. This is for a location that has people come in from different locations and once they leave they do not come back.

    Thank you again for all your help.
    Attached Files Attached Files

  59. #59
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Try this:

    Please Login or Register  to view this content.

  60. #60
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Thanks. I'll try that when i hey back to work on Friday. What about the other issue that involves both files?

  61. #61
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    I also put code in to Protect the Workbook - have I missed an issue??

  62. #62
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    The code doesn't seem to work for either file. Keep in mind that each file is set up slightly differently.

  63. #63
    Forum Contributor
    Join Date
    12-29-2014
    Location
    Indonesia
    MS-Off Ver
    Office 2010, 2013
    Posts
    125

    Re: Data transfer from one sheet to another multiple cells

    hi all, please allow me to join this thread. this thread so intrested.thank you

  64. #64
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    The new code is for the Medical Outs - am I supposed to recode the other book also??

  65. #65
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I might be putting the code in wrong but I can't get it to work. Also, I wanted both files to be protected from sheet deletions and developer mode when locked if possible.

  66. #66
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    This seems to work for the DA-2 book, so I just added the book protection - what isn't it doing?

    Please Login or Register  to view this content.

  67. #67
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    That worked for the DA-2 file except I can still access Developer mode without having to input a password. is there a way to add that in too? Plus, that code also worked for the Medical Out Counts. However, it also needs to have developer mode locked and when the "Assigned Housing" drop box is cleared all of the info needs to be removed to include the inmate name and number. It represents that the inmate has left Medical and returned to their actual housing area.

  68. #68
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    xladept,
    I haven't heard back from you in a while. Just wondering if you had any luck with my request.The attached file has your most recent modifications applied to it. Just a reminder, if possible, Developer mode needs to be disabled while the file is protected. Also, you made it that when "other (specify)" is selected for location it does not create the log entry until the cell next to it had data put into it. Can you apply that same rule to the term "Barber". Thank you for all your help. We are putting this on a trial run on Monday for 2 weeks to see how it works out for everyone. This would not be possible without you. Your time and effort are greatly appreciated.

    And the previous file attached on named "Medical Out Counts" needs to have the information removed when the Assigned Housing is cleared just like with "ATW" and "LOSS". This request is SPECIFICALLY FOR THAT FILE ONLY and no others. Thanks
    Attached Files Attached Files
    Last edited by demetrius323; 01-29-2015 at 09:03 AM.

  69. #69
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    Here's your new change code:

    Please Login or Register  to view this content.
    * I though that I already fixed the Medical Outs Issue with post #59 DemetriusMO??
    ** I don't know how safely disable the Developer Mode - you can't change the code with the book protected???

  70. #70
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    thank you, that code worked for that file. Also, I found that I can manually add a password protection to VBA. As for the Medical Outs file, when I put in the code from post #59 I kept getting an error. Try it for yourself. Download the Medical Outs file from post #58 and put in the code from post #59. Maybe it's me. Perhaps I put it in wrong. Just keep in mind that the data needs to be cleared just like the other files when "ATW" and "Loss" are selected. They are not a selectable option in this file but still needs to react the same way when the Assigned Housing data is cleared. Plus add in the workbook protection. Thank you again.

  71. #71
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    It seems to be working:Medical Out Counts.xlsm

  72. #72
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    for some reason it didn't want to work for me, but now it does. Thank you so much for all of your help. Can you private message me your name so I know who to give proper credit to? or do you want to leave it as your screen name? Either way, You have greatly helped out our Corrections Department and I truly thank you for all of the time you have spent with me on this project.

  73. #73
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Demetrius,

    You're welcome!

    My name is Orrin White

  74. #74
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Thank you Orrin. My name is Justin Hulme.

  75. #75
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I'm sorry to bother you again, but my Captains asked to have something edited. When "Loss" is selected they want it to react the same as "Other (specify)" and "Barber" in regards to requiring the extra cell of data, but once that extra data is entered they want it to behave normally and remove all the data. They want this because they want to know where an inmate was a "Loss" to, for example another housing unit. Sorry to inconvenience you with this but I know it should be an easy fix for you. Thanks.

  76. #76
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    This should do it:

    Please Login or Register  to view this content.

  77. #77
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Thank you so much

  78. #78
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    You're welcome!

  79. #79
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Sorry, it didn't quite work. It left some residual data in the cell next to the Location on the Accountability sheet.

  80. #80
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Sorry - needed to change the big program too:

    Please Login or Register  to view this content.
    Last edited by xladept; 02-07-2015 at 04:01 PM.

  81. #81
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Awesome, it works! Thank you!

  82. #82
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    You're welcome - let me know if I forgot anything else

  83. #83
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Trial run is going excellent so far. A couple of notes that were asked to be included in the spreadsheets. I was able do take care of one myself. However, I need your help for the other ones. I believe that they are fairly simple. On the attached sheets, the "Auto-Logs" have a new column called "Total Time Out" or "Total Time In" depending on which file you are looking at. They would like the time difference between the 2 to show the amount of time spent at that location. You will notice that the code is slightly different as I modified it a little to adjust for a new column on the Accountability sheet so keep that in mind. The other thing that is requested is for the "Auto-Log" to create a new sheet every month, not every year. It was stated that in the event of having to look up a specific thing, having to scroll through an entire year would be very tedious. Hopefully neither of these requests will cause a problem. So, as a re-cap

    1. "Total Time Out" or "Total Time In" on "Auto-Log" sheet

    2. Change from new "Auto-Log" sheets being created every year to every Month.

    Thanks
    Attached Files Attached Files
    Last edited by demetrius323; 02-08-2015 at 12:16 PM.

  84. #84
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Hi Justin,

    Try these:DemetriusMOV.xlsmDemetriusV.xlsm

  85. #85
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    I won't really know until next month, but this creates a new "Auto-Log" sheet every month? If so, you are the man!

    Just so you know, ever since we started working together I have been wanting to buy your book more and more every day. Hopefully soon I will be able to save up the money to purchase it so I can learn to do the things that you make seem so easy. It's like "Spreadsheet Sorcery"!
    Last edited by demetrius323; 02-08-2015 at 08:40 PM.

  86. #86
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Oh - Thanks Justin but I wrote that book awhile ago before I ever was voted Guru and, I think it's overpriced

  87. #87
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Well if that's the case, your don't happen to have a free one laying around do you? Lol. I am very anxious to learn but i want the best learning material fire the least amount of money. It's tough living paycheck to paycheck.

  88. #88
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Check out the books on Amazon by Julitta Korol who writes for Wordware, one of those may be what you're looking for!

  89. #89
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Ok. Thanks

  90. #90
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    I see that you can get:

    Microsoft® Excel® 2010 Programming By Example: with VBA, XML, and ASP (Computer Science)

    for $16.00


  91. #91
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Cool thanks

  92. #92
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Data transfer from one sheet to another multiple cells

    Replace this code in the last books I just posted:

    For the Medical Outs:Accountability Code

    Please Login or Register  to view this content.
    For the regular (original) book Accountability Code
    Please Login or Register  to view this content.
    *I realized upon reflection that there had been a name change necessitated and that there were some column changes to be dealt with:|

    I tested it by just changing the February To January in the Auto-Log.
    Last edited by xladept; 02-09-2015 at 05:50 PM.

  93. #93
    Registered User
    Join Date
    11-16-2014
    Location
    Kissimmee, FL
    MS-Off Ver
    2010
    Posts
    62

    Re: Data transfer from one sheet to another multiple cells

    Ok. Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Transfer data from cells with sums to another sheet
    By jimmydale912 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-01-2014, 01:47 PM
  2. Macro to Transfer data from multiple sheets into rows on a master sheet
    By serrone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2013, 06:35 AM
  3. Replies: 1
    Last Post: 03-11-2013, 05:37 AM
  4. Replies: 2
    Last Post: 05-11-2010, 09:36 AM
  5. Transfer data from multiple cells to new worksheet based on cell value
    By Lostinxcel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2010, 05:57 AM

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