+ Reply to Thread
Results 1 to 22 of 22

Copy & paste again

  1. #1
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Copy & paste again

    hi

    my following code will not work as I do not know how to phrase the first 3 lines in the correct syntax) but that's just what I'm trying to achieve: to copy several ranges in different columns simultaneously instead of separately, then PasteSpecial their values to another workbook at the starting at the target EZ2 mentioned all columns standing side-by-side to each other in that sequence.

    Can anyone point the way ?

    Please Login or Register  to view this content.

    Stewart Mann
    Last edited by MannStewart; 04-30-2021 at 06:02 AM.

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    411

    Re: Copy & paste again

    Have a try using function Union, like this:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    hi rollis13

    i've tried your Union method, but it's not picking up the rest of the rows with data, it's only copying the top row (row 2) data. And another problem is that the row2 pasted is not copied according to the macro specified sequence, it is copying according to the alphabetical sequence.

    I also tried xlUp but same problem only row2 was pasted.

    Wonder what's causing this

    Stewart
    Last edited by MannStewart; 04-30-2021 at 08:24 AM.

  4. #4
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit
    Posts
    267

    Re: Copy & paste again

    Quote Originally Posted by rollis13 View Post
    Have a try using function Union, like this:
    Maybe just need to qualify the ranges to wbSoun?

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    .. but the original code by rollis was already qualified with wbSoun, i dont think that was the cause. Besides, if the issue was that, it wouldn't even pick up row2 at all, but it does only row2 oddly.

    Anyway, even if that could be solved, i'm more discouraged by the fact it won't copy according to the sequence specified and just goes alphabetical order. That would make this method totally redundant and i'll have to go back to separately copy and paste 1 by line tediously like before
    Last edited by MannStewart; 04-30-2021 at 08:31 AM.

  6. #6
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    411

    Re: Copy & paste again

    Maybe with these changes it would work better, I have no way to test it on real data.
    Please Login or Register  to view this content.
    Thanks to @ByteMarks for pointing out a possible problem.
    Last edited by rollis13; 04-30-2021 at 08:31 AM.

  7. #7
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit
    Posts
    267

    Re: Copy & paste again

    Me neither, but that's what I was thinking it should be.
    Thanks

  8. #8
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    rollis13

    ok, that works, but the other issue remains, sequence not as specified in macro, Excel has pasted it alphabetically. is there any way to force the sequence according to macro ? It's now pasted in this seq:

    B - G, J, AD - AF

    Stewart
    Last edited by MannStewart; 04-30-2021 at 08:43 AM.

  9. #9
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit
    Posts
    411

    Re: Copy & paste again

    Ok, lets revert, this version will copy/paste in the right order:
    Please Login or Register  to view this content.
    Last edited by rollis13; 04-30-2021 at 04:27 PM.

  10. #10
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    MannStewart, try below code ...
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  11. #11
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    @Rollis13
    appreciate your suggestion, however considering the length of this code, it defeats my initial intention to accomplish the task in a compact 1 line or at least as simple as Union. I was already pasting them line by line in 3 pairs like what you did in between With, End with without needing to declare an additional 'lc', in my needed sequence by an inefficient separate copying & pasting, but for lack of a better solution, i'll just have to get back to square one for now

    cheers
    Stewart
    Last edited by MannStewart; 05-02-2021 at 10:22 PM.

  12. #12
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    @nankw83

    Your code did it, this is what i'm talking about .. it's brilliant

    In the actual code I actually had a clearcontents line preceding the body of the code, but i only just realised it's not working when i tried to paste the new data, as i noticed it's only clearing the row2 every time, i hope you could also point out what's wrong with it:

    Please Login or Register  to view this content.

    Stewart
    Last edited by MannStewart; 05-02-2021 at 10:23 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    Quote Originally Posted by MannStewart View Post
    @nankw83 .. i hope you could also point out what's wrong with it:
    Maybe ...
    Please Login or Register  to view this content.
    Or you could add the below line in my code provided earlier
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    got it ,
    but i'm just wondering why putting the ClearContents line between COPY & PASTE process did not create a disconnect for the COPIED data.

    So, if i have 2 sets of COPY & PASTE, with the second set in a different sheet, can I modify the code as follows? both with ClearContents per your method, but redeclared separately

    Kindly correct me if i'm wrong anywhere in the code:

    Please Login or Register  to view this content.
    Stewart
    Last edited by MannStewart; 05-03-2021 at 10:40 AM.

  15. #15
    Registered User
    Join Date
    05-03-2021
    Location
    Watertown, South Dakota
    MS-Off Ver
    2016
    Posts
    2

    Re: Copy & paste again

    Posting to this thread so I can follow it closely. Just joined this forum because I was looking for a way to do something similar to this. I want to prompt or input worksheet names and copy/paste the data in those sheets into a single sheet without overwriting and in the order prompted.

  16. #16
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    Quote Originally Posted by MannStewart View Post
    i'm just wondering why putting the ClearContents line between COPY & PASTE process did not create a disconnect for the COPIED data.
    I don't know if I understood you correctly but in my code, I am not doing copy/paste like a range, I am assigning a data to an excel variable (a) then putting certain columns of this variable into the sheet. I don't have a file to test your code but it looks like it should work fine

  17. #17
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    @crusader2100 , welcome to the forum

    Just a small tip, you can subscribe to any thread from the "Thread Tools" above the first post where you'll be able to track it & receive notification(s) on any updates

  18. #18
    Registered User
    Join Date
    05-03-2021
    Location
    Watertown, South Dakota
    MS-Off Ver
    2016
    Posts
    2

    Re: Copy & paste again

    Oh, that's even easier. Thank you, very much!!

  19. #19
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    @nankw83

    i see, so if Excel is not copying but assigning, why is the CutCopyMode = False line afterwards required? So for every set of assigning to do, i still need to add this Application CutCopyMode = False immediately afterwards ?


    2. Mainly, i need to double check to ask about this line, a third copied range starts at A5 in the source sheet:
    if i add a third copy range in another sheet where the copied range starts at GY5, must the variable 'a' still, and always start at column A? :
    Please Login or Register  to view this content.
    as long as Lr always refers to the last column number.

    Did i understand correctly?


    3. My last question is, what is the (3) inside the End command, is that a non-negotiable constant ?
    Please Login or Register  to view this content.

    Stewart
    Last edited by MannStewart; 05-03-2021 at 10:50 PM.

  20. #20
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    Quote Originally Posted by MannStewart View Post
    so if Excel is not copying but assigning, why is the CutCopyMode = False line afterwards required? So for every set of assigning to do, i still need to add this Application CutCopyMode = False immediately afterwards ?
    In my code, I'm not using copy/paste nor did I have CutCopyMode = False line. However, you can use copy/paste in VBA & use that line


    if i add a third copy range in another sheet where the copied range starts at GY5, must the variable 'a' still, and always start at column A?
    No, it depends on the structure of your file & where the data is located that you want to assign to variable 'a'



    as long as Lr always refers to the last column number. Did i understand correctly?
    No, Lr refers to Last Row not Column


    what is the (3) inside the End command, is that a non-negotiable constant ?
    This is just another way to write End(XlUp)

  21. #21
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    180

    Re: Copy & paste again

    @nankw83

    got it, thanks very much for your answers to all my questions


    Stewart

  22. #22
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,188

    Re: Copy & paste again

    Glad to help & thanks for the Rep+

+ 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. Copy text from a cell paste to website search box and copy & paste results in same sheet
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2020, 06:06 PM
  2. Replies: 3
    Last Post: 01-01-2015, 06:26 AM
  3. How do I make the copy/paste of one column contingent on the copy/paste of another?
    By Kwasimitsu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2014, 01:51 PM
  4. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  5. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2010, 10:34 AM
  6. Replies: 1
    Last Post: 01-04-2005, 06:06 PM

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