Cell data of One Sheet Select Automatically copies in a cell of Other Sheet.

  • Hi,

    I want to simplify something in my excel sheet. But don't know how to do it. The idea describes below.


    I have a large datasheet name bill. In this sheet Column, I9:I1200 has data but not in every cell.

    Now when I select one of the cell in between of I9:I1200 which cell has data,

    The vba code automatically copies the selected cell data to another two sheets name 'Bill Info' to D12 and sheet 'Other' to G13 for calculating those sheets.

    If multiple cells or the cell which have no data select in the range of I9:I1200, It returns nothing.


    Is it possible? If possible please let me know What will be the code.


    Thanks.

  • Try this, put it in the Worksheet Object Module for the data sheet "Bill"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You are so good, I am loving it.

    Wish I could do help the way you do.


    The above code is like magic,

    now,

    What if I select data I9:I1200 and selected row Y cell data also copied and paste to Sheets"Other" in cell G14 too.

    What should the code be?


    Thanks.

  • Try

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi Kjbox, Hope you are fine.


    The above code works fine in my workbook. But in some cases, I wish multiple cells to select and which will be copied and displayed to other sheets column-wise.

    The Source Sheet is the same "Bill" but the destination sheet change is "Number"

    Suppose I select 5 different cells in between I9:I1200 (i.e. I9, I13, I15, I122, I550) they will be copied and show in the "Number" Sheet as column-wise D9:D13

    if I select 3 different cells in between I9:I1200 (i.e. I9, I13, I15) they will be copied and show in the "Number" Sheet as column-wise D9:D11

    if I select 7 different cells in between I9:I1200 (i.e. I9, I13, I15, I122, I550, I1120, I1200) they will be copied and show in the "Number" Sheet as column-wise D9:D15


    Can it be done in the existing code you created above?


    Thanks in advance

  • It is not possible to adapt the code for use with multiple cell selection, unless the cells are continuous (e.g. I9, I10, I11)


    The only way would be to have the code assigned to a button, which would need to be clicked after the selection of either a single cell or multiple cells had been made

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Just to be clear, if a single cell is selected then copy is to sheet "Bill Info" and "Other", if multiple cells selected then copy is to "Number"?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Yes Kjbox,

    You are absolutely right. when multiple cell selected copy to "number". Other condition is unchanged for "Bill info and other".


    Sometimes the source file "bill" is auto fliterd or not.

    Thanks and appreciate your help.

  • Try the attached test file.


    Code assigned to the button

    Files

    • Oz_Mamun.xlsm

      (20.69 kB, downloaded 39 times, last: )

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Auto Filtering will have no effect, the code works on the cell(s) you select. Obviously you cannot select a filtered out cel!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for your feedback Kjbox, and the sample fie you created.

    In the sample file data copied row-wise but in the "number" sheet, the data should to copied column-wise start from D9:D11. It always starts at D9.


    When I select 5 cell data of "BILL" & after clicking the button it copied to D9 to the next 4 cells below in "Number" Sheet

    after that, If I select 3 data of "Bill" & after Clicking the button it copied to D9 to next 2 cells below in "Number" Sheet but there previous 2 data remain of the previous click. It should be refresh. only what data select remain there. if selection changes then the data will be change.


    Thanks again.

  • OK I understand, so the only data from D9 onwards on the Number sheet will contain only any previously pasted data?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • "Please read the above massage with the below"


    Hi Kjbox, Please can the code only made for the "Number" worksheet.

    If the button clicked it copied the selected data to the "number" worksheet, not "the other & Bill info" Sheet.

    The button click option workable only for "Number".


    Thanks

  • Yes it can, but you cannot use the Worksheet_SelectionChange code for copying a single cell to Bill Info and Other as well as having a button to copy just multiple selections.


    The reason being, as soon as you select the first cell of a multi-selection the the Worksheet Event code will fire.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • So a single cell selection copies to D9 in Number sheet, and multi-selection to D9 onwards?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Well that simplifies the code!


    Change the code assigned to the button to

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.