+ Reply to Thread
Results 1 to 19 of 19

copy cells from a row in one of several sheets into another sheet based on check box.

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    copy cells from a row in one of several sheets into another sheet based on check box.

    Hello. A few days ago, someone assisted me in creating a macro that enabled me to copy certain cells from one sheet to another when a check box was ticked. Works like a charm.

    I have another workbook where I want to do something similar. I thought I could easily substitute spreadsheet names and cell ranges to make the same macro work here. But I've not been successful. Attached is a shell of the workbook I'm working on now. Check boxes are there. This time I have several sheets and want specific cells from any checked rows on any of the sheets to move to the "programs requested" sheet at the end. I tried doing it with the first sheet (Brockton) as a test and had no luck. Here is the language below....Getting run time errors, among others. FYI, the cells I would like moved over when checked are A,B,D,N,P,R and S.

    Could someone assist me?
    Thank you!

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    None of the sheets mentioned on the code are on the attached.

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Sorry, I attached the wrong workbook. Here you go! It should be "for forum"
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Please Login or Register  to view this content.
    Sheets("Programs requested") is blank, so you will get an error

    where in the code does check for check box was ticked? I could not see anywhere.

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Set crng = Sheets("Brockton").Range("T3:T6")

    I know almost nothing about coding. This was done for me by another member of the forum. But I assumed that the code above referred to the check box.

    If I populate "Programs Requested" first, then that will take care of one error?

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Set crng = Sheets("Brockton").Range("T3:T6")

    Refers to 3 cells only, which means the code loops 3 times.
    Let's start from zero.
    you have sheet Brockton which has check boxes in T3:T27. All cells within this range have box, except some of the cells which have total in them.
    Do you want to copy A,B,D,N,P,R and S from that sheet to WHERE? which sheet? if a cell has a check box.

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Okay!
    You want to copy in sheet Programs requested

  8. #8
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Right. I actually want to do this from any of the sheets. When the box is checked on any of the sheets, those particular cells get copied onto "Sheet Programs". I was starting with "Brockton" to see if I could get the coding right. But all I get are errors.

  9. #9
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Hello again.

    I've updated the workbook, so that "Programs requested" is no longer empty. Is it possible to help me get the coding correct so that I am able to move those fields from the other sheets to "programs requested" if the box in column t is checked? Thank you so much.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    I am working on it. You want to copy from all sheets which have check box to the "programs requested" sheet.

  11. #11
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Yes. Thank you kindly. Please let me know if I can provide anything at all. I can't tell you how much I appreciate this!

  12. #12
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    You have checkbox. To test if there is a check box in a cell, we use True or False, but in your case, there are mixed. Some of the cells are ticked and others are not. I have ticked all of them so as to test a cell for consistency. I do not know if this you wanted, but try the attached.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    This is fantastic!
    This is exactly what I need. Thank you very, very much.

    I am reading the code and trying to figure out how you were able to identify the specific cells in the spreadsheets that I wanted to appear in "Program request" spreadsheet. I can't see where you have identified them specifically. If you have a moment, could you show me that line? I'm just very curious and would love to learn more.

    Thank you again!

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    I have now updated the code using your normal data with out touching the box( Without ticking the box).

    The lines which say
    Please Login or Register  to view this content.
    used to test if each cell fulfils these conditions.
    I can add the name of the sheet in to column A if you want so that you can easily see which lines comes from which sheet.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Sure. That would be very helpful.
    And if I decide down the road that I want to include another cell or remove one that I originally included, is it easy enough for me to edit the macro to do that?

    I am very pleased. Thank you again so much.

  16. #16
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    I want to include another cell or remove one that I originally included, is it easy enough for me to edit the macro to do that?
    Yes,
    When you run the code, it first clears any data in programme and repopulate it with new data. All You have to do is to check if the check box is ticked or not.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Wonderful. Final question. As I continue to add those checked cells to my "Programs Requested" sheet, I want to update the Programs Requested sheet and add columns with new information to each record. Yet I find that if I sort the Programs requested sheet, any time I run the macro anew, it inserts the new row where it belongs in the sort, but the information in the new columns stay put. In other words, the new data no longer goes with the record it is associated with. Is there a way to avoid this?

    Almost there! I am psyched.

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Not sure I understand.
    It is probably this line

    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    07-24-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: copy cells from a row in one of several sheets into another sheet based on check box.

    Thank you again for all of your assistance!

+ 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. [SOLVED] Copy cells from a row in one sheet to next row in second sheet based on check box
    By SMcuthbert in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-25-2013, 01:26 PM
  2. Check cell Value in range and copy specific cells to another sheet
    By jmethejedi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2013, 06:25 PM
  3. Copy rows from one sheet to different sheets based on cell value
    By Ireland316 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 04-26-2012, 06:17 AM
  4. Replies: 2
    Last Post: 02-21-2012, 09:21 AM
  5. copy cells from sheets based on date, paste to another sheet
    By upod in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-27-2009, 01:29 PM

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