+ Reply to Thread
Results 1 to 11 of 11

Macro (button) to copy and paste selected cells from one row to another row with text

  1. #1
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Macro (button) to copy and paste selected cells from one row to another row with text

    Hello all,

    i have a problem with an excel sheet and i'm new to VBA programming.
    I found allready out how to replace cells in my sheet.
    What i now look for is to copy first the cells i replace.

    example i have in cell J3 (B1) (B1=Batch1) and in cell L3 the name who takes the batch.
    so in line F14, F18, F22, F26 is filled in B1; what i need is that all filled cells with B1 from row F will be coppied into the fiels in row J = J14, J18, J22, J26.
    how can i get this running.
    The cells can change and the batches can also change; example B2=Batch2; B3=Batch3 and so on


    here my code right now:

    Please Login or Register  to view this content.
    Last edited by NBVC; 02-02-2012 at 02:42 PM. Reason: add code tags for newbie PM rule

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Will the copy always go from Column F to Column J? A sample workbook showing the raw data and a sheet showing the final result would make things easier - if you could post a sample workbook with no sensitive data it would make things easier. What you describe is easy if it is a simple copy but you say there can be different batches - how are they handled? A sample workbook with sample data/output would help answer these questions. Be very specific about what you want to achieve
    Hope this helps.
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

  3. #3
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Hello Anthony

    I created a sample workbook for you, if you can have a look and explain to me what you and why you have done it would be nice so i can learn a bit.

    Ok explain what i done:

    i marked the relevant cells for batches with colors, i also gave them names like B1 (1) for the first step B (2) for the second step and so on.
    as you see on row H there are 3 buttons called PICK, PACK, SHIP.
    if we click on button Pick that should move all named cells in row F, B1 (1) with a batch to the same cells in row J Packer and then do the replacing
    if we click on button Pack that should move all named cells in row J, B1 (1) with a batch to the same cells in row O Shipper and then do the replacing
    if we click on button Ship should do the replacing only.


    this sholud also work for the later drops on line 66 downwards

    Attachment 138803


    i hope this is not to confusing, if you need more information please let me know.

    Arthur

  4. #4
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Just one nit pick - F, J and O are columns not rows - just so we are talking the same language. Now if you select Pick is it only the values in column F that are marked B1 that get copied into column J, then next button Values marked B1 copied into Column O that are marked B1, With the button Ship you say replacing only - what do you mean by that? Could you show me a sample output sheet showing how the sheet should appear when the Pick button is selected. From what you describe I have just done the Pick button - see if this is correct.
    if we click on button Ship should do the replacing only.
    I do not know what you mean by that. As well should the times be entered into column K and L when the Cells are copied. You are obviously clear what you want to happen however I am not. A sample sheet just showing a few examples of how the sheet should appear when each button is selected will make things easier.
    I have just written code for the PICK button so see if it does what you expect and answer the questions above

    Hope this helps.
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Hello Antony,

    First of all i want to say thank you for your help.

    Just one nit pick - F, J and O are columns not rows - just so we are talking the same language.
    your right; Sorry for that, think i'm a bit confused of the codes i don't get running.

    Now if you select Pick is it only the values in column F that are marked B1 that get copied into column
    Yes only the Value in the cells of Column F should copied to the same cells of column J.

    then next button Values marked B1 copied into Column O that are marked B1
    yes that's what should copied from column J to column O

    With the button Ship you say replacing only - what do you mean by that?
    I wanted to mention that this cells do not need to copied, only replaced replaced with the values from the cell L10

    [quote]Could you show me a sample output sheet showing how the sheet should appear when the Pick button is selected. From what you describe I have just done the Pick button [\quote]

    I made some example sheets for you i hope this makes it more visible to you what i wanted to achive. I only had the Pick button done.
    If i try to click on your button i get an runtime error with this marked as error fCell.Copy Destination:=fCell.Offset(, 4)

    [quote]I do not know what you mean by that. As well should the times be entered into column K and L when the Cells are copied.[\quote]
    no because the picker takes the batches them self and we fill in the times when we get the time to fill in the workbook, but if you can tell me how i can set it if the end time gets filled in, in column H it auto fill in a yes in column I that would be great. Then i can changes this also for coulmn M and Q.

    [quote]A sample sheet just showing a few examples of how the sheet should appear when each button is selected will make things easier.
    [\quote]

    i made some example sheets and made a zip file i hope this is more clear for you.

    thank you for you help



    Arthur

  6. #6
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Here the remaining files

  7. #7
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Have a look at the attached workbook. It should do what you are after and thank you for going to so much trouble to explicitly show what you wanted. It makes everyone's job so much easier. With respect to filling in the times if you tell me how you would like that done automatically that should be relatively easy to code. Anyway make sure the attached workbook does what you are after. I have removed your macros from the code as I don't think you need them.

    Hope this helps.
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Hello Antony,

    Thank you for your help, that's more i've expected;you added also some messeage boxes.

    I only have 1 remaining question what code and where can i add it to clear the cells for the batches and names after clicking the buttons?

    i tried to add the clear selection but i get allways an runtime error; maybe that's because of your good code.

    thank you in advance

    Arthur

  9. #9
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Try the new attachment - the code is just to set the cells to vbnullstring ("")

    Hope this helps.
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-29-2012
    Location
    Netherland
    MS-Off Ver
    2010
    Posts
    9

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Hello Antony,

    thank you so mutch, the sheet works great.

    Problem solved


    best regards

    Arthur

  11. #11
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Macro (button) to copy and paste selected cells from one row to another row with

    Fantastic
    Anthony
    “Confidence never comes from having all the answers; it comes from being open to all the questions.”
    PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”
    Last edited by smuzoen; 02-02-2012 at 07:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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