+ Reply to Thread
Results 1 to 9 of 9

Copy multiple lists until last row and paste to unique lists

  1. #1
    Registered User
    Join Date
    10-14-2020
    Location
    Dublin
    MS-Off Ver
    Professional plus
    Posts
    44

    Copy multiple lists until last row and paste to unique lists

    Hello everyone,

    im stuck in a current task as I am new to VBA.

    In the example doc, there are 3 tabs.

    I want to be able to copy all the values of the 4 lists until their last row with values in the the "Copy" tab and paste them into the "Paste" tab in E3 as a unique list.

    My issue is that in my original doc(As i tried to reppresent in this doc) the values in the lists contain formulas with a iferror, and so when i try copy them and paste to last row, i get huge gaps between the lists.

    So essentially i want to copy all the lists until the last row with values and paste them.

    I hope i was clear with my explanation, if not i am more than happy to re explain.

    I thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,776

    Re: Copy multiple lists until last row and paste to unique lists

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Copy multiple lists until last row and paste to unique lists

    Another option ...
    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

  4. #4
    Registered User
    Join Date
    10-14-2020
    Location
    Dublin
    MS-Off Ver
    Professional plus
    Posts
    44

    Re: Copy multiple lists until last row and paste to unique lists

    OMG Thank you BOTH!!! Geniuses!
    But this one seems to work perfectly fine!

    I just Beg you for one last thing...im sorry to be such a pain.

    I was trying to apply this to my main doc, and it selects the first list, but when going to the second one it crashes with the following error: Type Mismatch

    This is the change i made :


    Please Login or Register  to view this content.
    Consider that as shown the lists start at column a25 etc.
    I dont know what the problem could be.

    And QQ, if I have more lists to add id just simply edit this from For x = 1 To 4 to For x = 1 To (whatever number of lists) and then add the column numbers here right? Col = Choose(x, 0, 7, 13, 19)

    Im sorry to bother you more.
    Last edited by alansidman; 12-16-2020 at 12:07 AM.

  5. #5
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Copy multiple lists until last row and paste to unique lists

    Without a sample file it is a bit difficult to know the error but I can see couple of changes that you made
    1. In your sample file, your header was in row #4 & now it looks like its in 25 so you need to adjust the -3 to -24 in the Lr
    2. Do you have any data in column A ?
    3. For the number of columns, yes you are in the right track ... For x = 1 to (whatever number of lists)

    If you can post a sample file with the exact same structure of your original file, we could assist you more

  6. #6
    Registered User
    Join Date
    10-14-2020
    Location
    Dublin
    MS-Off Ver
    Professional plus
    Posts
    44

    Re: Copy multiple lists until last row and paste to unique lists

    Yes that seems to have do something, but it still gives me an error when i get to this part after the first run
    ->
    Please Login or Register  to view this content.
    Again i do apologies to waste your time on this, but this is probably the most helpful thing ive found so far.

    I have attached the sample doc.

    Again, thank you in advance.
    Attached Files Attached Files
    Last edited by alansidman; 12-16-2020 at 12:08 AM.

  7. #7
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Copy multiple lists until last row and paste to unique lists

    Based on your 2nd sample file in post #6 ... Try below code noting that I have changed the sheets names (file attached)

    Please Login or Register  to view this content.
    Edit : Or perhaps a more simplified way ...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nankw83; 12-16-2020 at 12:06 AM.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Copy multiple lists until last row and paste to unique lists

    @Nicolantonio

    My apologies for posting the wrong item here. I have removed that post.
    Last edited by alansidman; 12-16-2020 at 04:57 PM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Registered User
    Join Date
    10-14-2020
    Location
    Dublin
    MS-Off Ver
    Professional plus
    Posts
    44

    Re: Copy multiple lists until last row and paste to unique lists

    Hello Alan,

    I believe you have mistaken me with someone else, becausei do not post excel content anywhere but this forum & I posted this question only here.

    I did a deep search online before posting this and i couldnt find anything, because most of the similar questions contained lists attached to each other and my question was for separated lists with random content between them.

+ 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] Multiple Unique Distinct Lists
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2013, 11:53 AM
  2. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  3. Validation Lists - Multiple dependent lists with unique values
    By Lewigi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 06:42 AM
  4. [SOLVED] Compare two lists and copy unique values to the second list
    By Brumbot in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-06-2013, 07:36 AM
  5. LISTS! identifying unique values in multiple lists
    By pwall1115 in forum Excel General
    Replies: 3
    Last Post: 04-09-2013, 03:36 AM
  6. [SOLVED] LISTS! how to identify which values are unique in multiple lists
    By pwall1115 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2013, 10:43 PM
  7. [SOLVED] Multiple dependent lists with unique results
    By Jeepster325 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-05-2013, 01:14 PM
  8. macro copy/paste lists data from multiple sheets to one comparison sheet
    By johnnieboi89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2011, 04:22 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