+ Reply to Thread
Results 1 to 12 of 12

Copy double-clicked cell to next empty row in different sheet with each double-click

  1. #1
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Copy double-clicked cell to next empty row in different sheet with each double-click

    I am trying to create an inventory system.

    I have written code so that if you double click a product (Sheet1), it adds the relevant information about the product (columns 1,2,3, 9, 10) into the purchase order (Sheet 4).


    I also have code so that it only adds a secondary product if it is from the same supplier (as seen by the value of Row 22, Column 6).

    I need to find a way such that the first product double clicked is added to Row 15, Column 3 of Sheet 4. If another product is double-clicked a new row is inserted below row 15 in Sheet 4 and the relevant information is copied, etc...

    Any help would be great!

    This is what I have so far:
    Please Login or Register  to view this content.
    Last edited by erhathaway; 08-21-2013 at 08:49 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Hi

    That code looks fine to me based on what you described. Can you please upload a copy of your workbook, including somewhere in the workbook showing me the result that you expect to see after this macro is run and what it is doing now. This will allow me to step through your code in the same context as you, just to make sure that I am getting the correct solution for you.

    Thanks

  3. #3
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Sure!

    Attached is the workbook. Let me know if I can help clarify anything.

    Product Catalogue - Test.xlsm

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Hi,

    Here is a working version of what I think you want. Please take a look at the changes I made to the VBA and see if it is what you are asking for.

    Notes:
    1. The vendor name is now in a named range called "VendorName" - this is important as it is being used in VBA
    2. The cell that says "Shipping" is now in a named range called "Shipping" - this is important as it is being used in VBA
    3. The formula that calculates the total value of the PO has changed now - this is important as the sum range is variable if you are constantly adding rows to the order form

    Let me know if this is what you are after.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Thanks! That is exactly what I was looking for.

    I am also curious as to how I could press a button (called "Save PO") on the Purchase Order sheet and have it take all the products in the Purchase Order and copy them to another sheet ("Orders Placed")

    I understand most of the code needed to make this happen, but I don't understand how to copy the products from the PO given that the size of the sheet is changing depending on how many items were added.

    Thanks again.

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Hi again,

    The following code can be inserted on a button click event to copy the product rows to a worksheet called "Orders Placed":
    Please Login or Register  to view this content.
    It is important to note that I have only given you enough code to copy these lines, as this was what you weren't sure about. I've left the formatting and other data copying up to you. This code also assumed that the required worksheet already exists, so you may want to perform a check first and if it doesn't exist, then have the VBA code create it for you, and then copy the headings across.

    Once your purchase order has been saved, you could then call the following subroutine to reset the purchase order back to its original state:
    Please Login or Register  to view this content.
    Hope this helps

  7. #7
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Thanks again! This is all very helpful and informative!

    I have one last issue: the sum that calculates the total doesn't seem to work if more than 4 products are added...

  8. #8
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    What formula do you have in the cell?

    I changed it to the following so that it accommodates the insertion of rows above:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know

  9. #9
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    I plugged that in and couldn't get it to work.

    As a reminder I want to set the value of column K equal to the value of column A x column J. All the values in Column K are summed and added to shipping and tax to give the value of Total.
    Please Login or Register  to view this content.
    What do you think is wrong? Thanks!

  10. #10
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Ah sorry about the misunderstanding, I thought the "TOTAL" cell was giving you the wrong value, I didn't realise that you were referring to the "Calculate Total" button.

    In that case, change the subroutine to reset the purchase order (if you decided to use it) to
    Please Login or Register  to view this content.
    and change the Calculate Total subroutine to
    Please Login or Register  to view this content.
    Hope this works now

  11. #11
    Registered User
    Join Date
    08-29-2012
    Location
    Calfironia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Ah great. Thanks! It works perfectly now.

    I have another question:
    I am trying to create radio buttons that control whether the double click event is capable of occurring. I've created the radio button code:
    Please Login or Register  to view this content.
    And I have nested an If statement in the beforedoubleclick event sub:
    Please Login or Register  to view this content.
    But now the feature of adding a product by double-clicking doesn't work. I can't figure this out...

  12. #12
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copy double-clicked cell to next empty row in different sheet with each double-click

    Hi, that's good to hear.

    Could you please upload an updated version of your workbook with the radio buttons for me to have a look?

    Thanks

+ 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] Load web image when cell holding URL is clicked/double-clicked
    By jchamber00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2013, 09:13 PM
  2. Double Click cell to jump to sheet
    By neodjandre in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-11-2008, 10:18 AM
  3. How to copy the value of cell into clipboard by double click
    By lassang01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-24-2007, 05:01 PM
  4. how to copy on double-click the cell value to Clipboard
    By cyrille in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-15-2005, 06:45 PM
  5. how to copy on double-click the cell value to Clipboard
    By cyrille in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-11-2005, 08:06 PM

Tags for this Thread

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