+ Reply to Thread
Results 1 to 15 of 15

Macro to cut and paste into a different worksheet

  1. #1
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Lightbulb Macro to cut and paste into a different worksheet

    Hi All, I've tried making this macro by recording the clicks, but that only works for the same cells every time. Attached is a spreadsheet that I use to track recipes that I make on a daily basis. I am trying to create a macro that, when run, it will cut and paste the cells that I hand select into another worksheet.

    Source Worksheet: "Recipes"
    Destination Worksheet: "Queue"

    So I would like to be able to select a varying amount of rows, in this case (attached document), I would select Rows (12:22) in "Recipes" worksheet, then run the macro, which would cut and paste those selected cells into the "Queue" worksheet. An important point to note, is that there always needs to be 1 blank row in between each recipe, so when pasting, it will need to paste 2 rows beneath the previous recipe, unless of course it is the first recipe being pasted into the "Queue" worksheet, then it can go directly beneath the header. Sorry if that is confusing, but I haven't the slightest clue how to write that macro, even with the constant help from you guys and YouTube tutorials.

    Thanks!
    Attached Files Attached Files
    Last edited by Keegan1116; 10-14-2020 at 01:20 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    Hi Keegan.

    That should be quite easy.

    However I think that we can improve on your stated requirement.

    This will work as you describe it. However I have written it to allow you to select a single cell and it will copy from that cell to the next empty row.

    I have modified the code this so it extends your selection up to the first empty row above.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-14-2020 at 02:45 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    Will do mehmetcik! Thanks

  4. #4
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    Hi Mehmetcik, I am getting a Run-Time error '9': Subscript out of range for the below part of the code.

    Please Login or Register  to view this content.
    Also to clarify, you mention in the macro I only need to select one cell; does it mater which cell? or the first cell in the selection?
    Last edited by Keegan1116; 10-14-2020 at 02:14 PM.

  5. #5
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    Hi Mehmetcik, I'm having difficulty understanding what you meant in your edit to your comment where you posted the macro. You mentioned that:

    Please Login or Register  to view this content.
    I'm trying to have the macro select the entire recipe (columns A:L) all the way down to 1 row beneath where the "Total:" (flavor used) is showed, then cut and paste into the "Queue" worksheet. The trouble is, some recipes have 3 flavors, whereas some have up to 10. So that's the reason for me wanting to simply select the rows manually and run the macro. I'm not entirely sure if that's what you're attempting to accomplish but I thought it might be wise to clarify if I wasn't accurate in my first description.

    In any case, the macro keeps giving me run-time errors, I must be doing something differently than you. I've tried selecting all rows manually, I've also tried just selecting the first cell with text in the recipe, which also didn't work. Will keep trying different things!
    Last edited by Keegan1116; 10-14-2020 at 02:45 PM.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    This Version will allow you to select rows like you describe.

    The 10000 is approx the number of cells in a row ( 16000 in my version) so you can select one cell, one row or multiples.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-14-2020 at 03:00 PM.

  7. #7
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    Odd - I am now getting the Run-Time error '438': Object doesn't support this property or method (this was with selecting the rows manually, as well as selecting one cell, the first cell with text in the recipe).

    Please Login or Register  to view this content.
    What exact method are you using before running the macro that works for you? Which cell do you select?

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    I select any cell in the recipe. that works for me.

    I have selected a single row in the recipe and that works for me.

    Let me try selecting multiple rows.

    I selected rows 26 to 37 and that worked for me.

    So tell me what you select and see if I can replicate the error.
    Last edited by mehmetcik; 10-14-2020 at 03:16 PM.

  9. #9
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    Hmm. Cells 26 to 37 in the "Recipes" tab? On the document that I attached, that is halfway down one recipe, and halfway into the next recipe. I'm not so sure were using the same document though I can see why the cells would be different. I'm guessing that's why it wont work for me?

    I attached the one I'm working on with the macro included (you should be able to see the error).
    Attached Files Attached Files
    Last edited by Keegan1116; 10-14-2020 at 03:24 PM.

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    Hi

    I selected rows 12 to 20 and it worked.

  11. #11
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    What the heck?!? It doesn't work for me! I selected rows 12:22 (dragging my cursor to select them all) then ran the macro, and got the same bloody error for this part of the code:

    Please Login or Register  to view this content.
    I'm on version (2010) 14.0.7258.5000, don't know if that makes a difference or not. I'm really at a loss here haha

  12. #12
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    wait while I try that.

    It worked for me.

    maybe the error is before that. Does your version of excel have the TextJoin function?

    This version gives you the Textjoin Function.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-14-2020 at 03:59 PM.

  13. #13
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    I'm not entirely sure, it does not come up when I begin to type it into a cell, so I don't think so. Is there an alternative to TEXTJOIN I can try to see if that's the issue? I really appreciate all your time and help.

  14. #14
    Forum Contributor
    Join Date
    02-18-2020
    Location
    Ottawa, ON
    MS-Off Ver
    Professional Plus 2010
    Posts
    116

    Re: Macro to cut and paste into a different worksheet

    IT WORKED!!! Thank you very much. You, are legend.

  15. #15
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to cut and paste into a different worksheet

    This version gives you the Textjoin Function.

    You can add the Textjoin function to your personal workbook and it will always be available for you to use in all your spreadsheets.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-14-2020 at 04:07 PM.

+ 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. Replies: 5
    Last Post: 11-10-2014, 11:49 PM
  2. Macro to Paste Entire Row from 1 worksheet to another worksheet using a condition
    By Phil_Packer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2014, 03:48 PM
  3. [SOLVED] Macro to Copy Master Worksheet create and paste in new auto named worksheet
    By TheRealLethality in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2014, 12:46 AM
  4. [SOLVED] Copy and paste from one worksheet to another worksheet macro problem
    By Kerbazee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-19-2013, 04:15 AM
  5. Macro to copy data from one worksheet and paste to another worksheet
    By robertmiller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 09:56 AM
  6. Need Macro to copy and paste the value of a row on 1 worksheet to another
    By be_bek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-24-2012, 09:46 AM
  7. Macro to cut a row from one Worksheet and paste in another
    By decampo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-22-2007, 10:31 AM

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