+ Reply to Thread
Results 1 to 9 of 9

VBA to Copy and Paste with certain criteria.

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Post VBA to Copy and Paste with certain criteria.

    Hi,

    I am new to VBA and Macro.

    I have a columnwise data, which I want to copy range of cells and paste values with certain criteria to specified cell using VBA.

    My Data:

    A B C D E F G H I J
    1 1 1 2 3 3 4 5 6 7 7
    2 2 2 3 4 4 5 6 7 8 8
    3 3 3 4 5 5 6 7 8 9 9
    4 4 4 5 6 6 7 8 9 0 0
    5 5 5 6 7 7 8 9 0 1 1

    What I am trying is if A1 and B1 is matching, then copy A1:B5 and paste it to cell A15, then goto next two columns i.e. C1 and D1, if C1 and D1 is matching then copy C1:D5 and paste it in C15 as it the blank cell after pasting data in A15.

    If A1 and B1 is not matching, then copy only A1:A5 range and paste it to cell A30, then check B1 and C1 and repeat the process if it is matching/not matching till the last column with data.

    I tried with the below code for two columns but not able to make out how to repeat the process further.

    Please Login or Register  to view this content.

    All your help will be much appreciated.

    Thanks in advance.

    Pradeep

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to Copy and Paste with certain criteria.

    Hi,

    Please Login or Register  to view this content.

    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA to Copy and Paste with certain criteria.

    Maybe:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: VBA to Copy and Paste with certain criteria.

    Quote Originally Posted by karedog View Post
    Hi,

    Please Login or Register  to view this content.

    Regards
    Thank you so much Karedog, your code is working as required, but if the 4th and 5th column (D & E) has a matching value, then result is showing in UnMatched section.

    My Data:
    A B C D E F G H I J
    1 1 2 3 3 4 5 6 7 7
    2 2 3 4 4 5 6 7 8 8
    3 3 4 5 5 6 7 8 9 9
    4 4 5 6 6 7 8 9 0 0
    5 5 6 7 7 8 9 0 1 1

    Result after executing the above code:

    Matched (A15): This is working fine.
    1 1 7 7
    2 2 8 8
    3 3 9 9
    4 4 0 0
    5 5 1 1

    UnMatched (A30): Here 2nd and 3rd column should have come in matched section.
    2 3 3 4 5 6
    3 4 4 5 6 7
    4 5 5 6 7 8
    5 6 6 7 8 9
    6 7 7 8 9 0

    Basically, I want to have all the duplicate columns in A15 and unique columns in A30.

    Thanks
    Pradeep

  5. #5
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: VBA to Copy and Paste with certain criteria.

    Thank you John for the code, but I am not getting the desired result as Karedog.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA to Copy and Paste with certain criteria.

    You're welcome. Yes, I think I may have misread your requirements, but at least you have a working solution.

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to Copy and Paste with certain criteria.

    You are welcome, and many thanks for the reputation points.
    Previously I thought that you want to compare them in pairs (Column A with B only, column C with D only, etc).

    Please try this code instead :

    Please Login or Register  to view this content.

    Regards

  8. #8
    Registered User
    Join Date
    02-02-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: VBA to Copy and Paste with certain criteria.

    Quote Originally Posted by karedog View Post
    You are welcome, and many thanks for the reputation points.
    Previously I thought that you want to compare them in pairs (Column A with B only, column C with D only, etc).

    Please try this code instead :

    Please Login or Register  to view this content.

    Regards
    WOW!! Perfect Karedog, this works like a charm. Thank you so much.

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA to Copy and Paste with certain criteria.

    You are welcom Pradeepdeepu, glad I can help, and thank you so much for the reputation points.



    Regards

+ 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 paste row that match criteria
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-05-2015, 08:40 AM
  2. [SOLVED] Copy and Paste with certain criteria
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-31-2014, 10:58 PM
  3. Replies: 0
    Last Post: 10-29-2013, 09:38 AM
  4. [SOLVED] Copy and Paste with criteria
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 12:25 AM
  5. [SOLVED] Copy paste depending on certain criteria (VBA)
    By bonny24tycoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2012, 07:59 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