+ Reply to Thread
Results 1 to 15 of 15

Transfer specific columns from one sheet into two sheets

  1. #1
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Transfer specific columns from one sheet into two sheets

    Hello everyone
    Thanks in advance for taking the time to read my post and for any assistance offered.
    I have 3 sheets in a workbook (source sheet - successful sheet - Unsuccessful sheet)
    Issue: I need to write a macro that will Transfer six columns of data if a condition is met. The condition is "Y" in column K in source sheet. If "Y" exist in Column K then Transfer the Columns from column A to column F & M to the Columns from column A to column G in successful sheet ... and If "Y" does not exist in column k then Transfer the Columns from column A to column J & M to the Columns from column A to column K in Unsuccessful sheet.
    I have tried to write a macro but have had no success.Please have a look at the sample workbook ... If possible I need to avoid loops as original data is too large
    I really appreciate your help in this regard.Thanks a lot
    Attached Files Attached Files
    Last edited by menajaro; 01-23-2019 at 01:40 PM.

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

    Re: Transfer specific columns from one sheet into two sheets

    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
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    Thanks a lot for your reply
    After testing the code on the original file ( with a lot of rows - about 20000 rows ) It took a very very, very long time
    Is there a faster way to achieve that? Thanks in advance.

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

    Re: Transfer specific columns from one sheet into two sheets

    If your file uses formulas, you could try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    the same problem and I do not use any formulas in this file

  6. #6
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    I tried to complete my idea but could not do it
    Perhaps there is a better and faster way to achieve that? Any help is greatly appreciated! Thank you.

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

    Re: Transfer specific columns from one sheet into two sheets

    See if this version helps:
    Please Login or Register  to view this content.
    Do you have any conditional formatting in your sheet? By the way, how long is it taking to run?

  8. #8
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    Thanks a lot for your reply
    I appreciate you taking the time to answer my question But I want to improve speed code's by using array
    I Do not have any conditional formatting in my workbook, As for the run time it took about 25 minutes.
    I welcome any ideas to improve the code.Thanks in advance.
    Last edited by menajaro; 01-24-2019 at 11:51 PM.

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

    Re: Transfer specific columns from one sheet into two sheets

    There may be a way of speeding up the macro but I'm afraid that I don't know what that would be. Perhaps you could start a new thread posting the macro and ask if there is a way to speed it up. There may be another forum member who could help. If you do find a solution, I would appreciate it if you could send me a private message with the link to that solution. I'm always looking to improve my VBA skills.

  10. #10
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    I would be very thankful to all of you for your help.

  11. #11
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Transfer specific columns from one sheet into two sheets

    Hello Menajaro,

    Here's another option which could be a little faster:-
    Please Login or Register  to view this content.
    The code also sorts the data based on Column K. Sorting helps to speed things up. I'm not sure if you want to sort though. I have also unmerged rows 10 and 11 as merged cells create problems with macros. The headings in the sample are now in row11 with data starting in row12.

    You'll note that I've used the sheet codes in the macro rather than the sheet names.

    I've attached your sample file with the code implemented. I've also added approx. 30K rows in the sample just to see how it works and it took just over two seconds to execute. I've included a timer in the code above so you can see how it works in your actual workbook.

    Please test it in a copy of your workbook first.


    I'm sure if you place a sort line of code in Mumps' code, it will execute much faster.

    I hope that this helps.

    Cheerio,
    vcoolio.

    P.S. The file didn't attach as its size is over the allowable limit so please test the code above in a copy of your workbook and let us know how it works for you.
    Last edited by vcoolio; 01-25-2019 at 07:09 AM. Reason: Add P.S.

  12. #12
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    Thanks a lot Mr. vcoolio,
    In fact, the issue has been resolved in another forum, Unfortunately not allowed to publish any links, images, or videos.
    Many thanks and kind regards
    Last edited by menajaro; 01-28-2019 at 09:30 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Transfer specific columns from one sheet into two sheets

    Hello Menajaro,

    You're welcome and thanks for the rep.

    For the sake of anyone who comes by this thread in future looking for a similar solution, could you please post the code in this thread that resolved the issue for you.

    Thank you.

    Cheerio,
    vcoolio.

  14. #14
    Registered User
    Join Date
    01-22-2019
    Location
    Poland
    MS-Off Ver
    2007
    Posts
    17

    Re: Transfer specific columns from one sheet into two sheets

    Welcome vcoolio & Mumps1
    In fact I've got what I want from Mr Doc.AElstein
    He's really a great personality
    I've tested the solution and the run time took only two seconds on about 20,000 rows and about 200 columns.
    Please Login or Register  to view this content.
    Best regards for all of you
    Last edited by menajaro; 01-28-2019 at 09:32 PM.

  15. #15
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Transfer specific columns from one sheet into two sheets

    Thanks Menajaro and thanks to the "Doc" also.

    Cheerio,
    vcoolio.

+ 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] vba code to merge headers from 2 sheets at their specific columns position 3rd sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2017, 11:41 PM
  2. Replies: 2
    Last Post: 03-13-2017, 05:58 PM
  3. Compare Columns in Two Sheets. If Match, Transfer data in Cell from Sheet 1 to Shet 2
    By beconajoonu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2016, 12:32 AM
  4. Transfer data Specific a row from multiple sheets to another sheet
    By salmasaied in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2016, 02:23 AM
  5. [SOLVED] Transfer Rows with specific color from Multiple Sheets onto one sheet
    By bukejr in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-19-2012, 09:49 AM
  6. [SOLVED] Transfer a specific cell from multiple sheets to a separate sheet
    By johnnylzep in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2012, 08:12 AM
  7. [SOLVED] VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-08-2012, 01:28 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