+ Reply to Thread
Results 1 to 7 of 7

If check box is checked, then copy specific cells to new sheet

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    If check box is checked, then copy specific cells to new sheet

    Hello everybody,

    I've been working on a template to create an estimate based on checked items in the ItemList sheet. So far I was able to copy the entire row of the checked items to the estimate sheet. What I'm actually trying to do are two things: (1) be able to copy specific cells from the row of the checked items (Cells from columns A, C, E, G) and (2) then paste them and present the information the same way it's displayed on the Estimate sheet. On the Estimate sheet, you can see that the Group Name has been inserted on top, while the checked items are listed beneath it, and finally there's a formula to sum the total amount of that specific group. Any help on this would be appreciated alot.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,419

    Re: If check box is checked, then copy specific cells to new sheet

    Here is the revised code for the copy selected rows button

    I would urge you to use the Option Explicit at the top of every code to ensure that you find any compile errors for undefined variables.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: If check box is checked, then copy specific cells to new sheet

    That works great, thanks alansidman! But what about the second step which is to identify which group the checked items belong to, list it on the Estimate sheet, and then subtotal the item amounts of the checked items?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,419

    Re: If check box is checked, then copy specific cells to new sheet

    On the Estimate sheet, you can see that the Group Name has been inserted on top,
    I don't see this on the uploaded workbook so I don't know what you are looking for here.

    and finally there's a formula to sum the total amount of that specific group.
    Until the first one is in place above, then I don't see how to do the second one.

    Do you want the Group Name to come over with the other data, and then do a SUMIF calculation by group?

  5. #5
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: If check box is checked, then copy specific cells to new sheet

    Excuse me for not uploading the correct file. I've forgotten to add the Group Name and Subtotal on the Estimate sheet. Would you kindly take a look at it and let me know what do you think?
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,419

    Re: If check box is checked, then copy specific cells to new sheet

    ok. If this were my project, I would do it a little differently. I would bring the Group over to the estimate sheet and put it in the left column and move everything else over one column. Next after I had completed the copy and paste selections. I would sort the data by Group and then either Pivot the data or use the Sub-Totals function within Excel to create the grouping.

    The result would look like this:

    Data RangeData Range
    A
    B
    C
    D
    E
    F
    1
    Group
    ITEM NAME
    DESCRIPTION
    QTY
    UNIT PRICE
    TOTAL PRICE
    2
    3
    Group 1
    P9-46156
    Item description
    1
    6740
    6740
    4
    Group 1
    P14-R101
    Item description
    2
    1240
    2480
    5
    Subtotal Group 1
    9220
    6
    7
    Group 2
    P17-R008
    Item description
    1
    1650
    1650
    8
    Subtotal Group 2
    1650

    or like this in a Pivot Table

    Data RangeData Range
    A
    B
    C
    D
    E
    F
    8
    9
    Data
    10
    Group
    ITEM NAME
    DESCRIPTION
    Sum of QTY
    Sum of UNIT PRICE
    Sum of TOTAL PRICE
    11
    Group 1
    P14-R101
    Item description
    2
    1240
    2480
    12
    P9-46156
    Item description
    1
    6740
    6740
    13
    Group 1 Total
    3
    7980
    9220
    14
    Group 2
    P17-R008
    Item description
    1
    1650
    1650
    15
    Group 2 Total
    1
    1650
    1650
    Alan
    Last edited by alansidman; 03-10-2014 at 11:38 AM.

  7. #7
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: If check box is checked, then copy specific cells to new sheet

    Hello Alan,

    Thank you for your help. I will try both suggestions and see which fits my needs best. Excellent advice.

+ 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. Check Box that shows options in Combo Box if Checked, and Hides if Not Checked
    By dumbjodie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-13-2013, 10:06 AM
  2. [SOLVED] When checkbox is checked it does not copy to new sheet. Only if not checked using false
    By thelisa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2013, 07:59 AM
  3. 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
  4. Replies: 2
    Last Post: 12-19-2012, 11:23 PM
  5. Copy data against each checked check box and paste in a new excel workbook
    By rnd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2010, 06:04 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