+ Reply to Thread
Results 1 to 22 of 22

Copy paste Values based on criteria

  1. #1
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Copy paste Values based on criteria

    Hi,

    Folks, I need one more VBA code for Copy Paste Value from you and I really appreciate your help!
    So, as you can see in this example I want, based on Criteria in Sheet1, cell A1 to Copy paste Values for All rows that have that number in A1.
    E.g. if I select 2 in A1, Sheet1 then I want from macro to Copy paste Values in Sheet "Inventory list" in all rows that have number 2 in column A.

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy paste Values based on criteria

    Where is the copied cells to be copied? which sheet name?

  3. #3
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Nowhere. I just want to select that rows and to do Copy--->Copy paste as Values. Just to run over formulas.

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

    Re: Copy paste Values based on criteria

    Start by placing a header such as "Number" in cell A4 of "Inventory List". Then copy and paste this macro into the worksheet code module. Do the following: right click the tab for sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in A1.
    Please Login or Register  to view this content.
    This macro will copy to Sheet1.
    Last edited by Mumps1; 04-14-2016 at 01:48 PM.
    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.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy paste Values based on criteria

    If you want to copy to sheet 2
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Quote Originally Posted by Mumps1 View Post
    Start by placing a header such as "Number" in cell A4 of "Inventory List". Then copy and paste this macro into the worksheet code module. Do the following: right click the tab for sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in A1.
    Please Login or Register  to view this content.
    This macro will copy to Sheet1.
    Maybe I am doing something wrong, but I get error every time. Could you please attach the file with macro?

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

    Re: Copy paste Values based on criteria

    Click this link to download your file: https://app.box.com/s/w9nd3abfbj3m5jkdz2kcd64ffoxh8uul

  8. #8
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Sorry man you misunderstood me. I don't need to Copy Cells anywhere, I want if I selected e.g. 3 in Sheet1 then go to Sheet "Inventory List" ---> select all rows where number 3 is in Column A ----> Copy----->Copy paste Values and that is all. I don't want to copy that cells in Sheet1.

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

    Re: Copy paste Values based on criteria

    Oops! Try:
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Little trouble.
    Please open the attachment and see.
    In every second row it puts me Number 2 instead of Copy Paste Values?!
    Attached Files Attached Files
    Last edited by toci; 04-14-2016 at 02:30 PM.

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

    Re: Copy paste Values based on criteria

    You are very welcome.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy paste Values based on criteria

    Remove these lines from my code.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Quote Originally Posted by AB33 View Post
    Remove these lines from my code.
    Please Login or Register  to view this content.
    This code doesn't work for me.

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

    Re: Copy paste Values based on criteria

    I'm sorry but I don't know what you mean. When you choose a number from the drop down list in Sheet1, the formulas in all the lines in sheet "Inventory List" that have the chosen number in column A are overwritten with the existing values. Unless I misunderstood, I believe that is what you were asking.

  15. #15
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    Plase open this file and go to "Inventory List". in Sheet1, I've already selected filter 3. As you can see in "Inventory List" row 3 is OK, but row 12 is NOT. Also row 17 is OK and row 22 in NOT.
    Attached Files Attached Files

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

    Re: Copy paste Values based on criteria

    I had another look at the sheet and you have conditional formatting in column B which for some reason is placing a number and red flag in the cells. When I removed the conditional formatting it seems to work properly. I have no idea why this is happening.

  17. #17
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    No man. I cleared Conditional Formatting as well but still have the problem in every second row based on Criteria in A1. Could you send me again your file to check?

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

    Re: Copy paste Values based on criteria


  19. #19
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    As I can see you have the same problem. Check now this file. I filtered for 4. In "Inventory List" compare row 8 and 13. Row 13, cell C13 before macro there was "IN0004" and now is 4. And if you look at C8, it is OK.
    Attached Files Attached Files

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

    Re: Copy paste Values based on criteria

    Give this a try:
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    348

    Re: Copy paste Values based on criteria

    This is the best version so far, but is it possible to exclude filtering mode in Sheet "Inventory List" as I need to see all rows?
    Thanks.

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

    Re: Copy paste Values based on criteria

    This will remove the filter.
    Please Login or Register  to view this content.

+ 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. Alternative to copy and paste values based on criteria
    By krishns in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-10-2015, 12:07 PM
  2. Replies: 1
    Last Post: 10-05-2014, 12:28 PM
  3. [SOLVED] copy Tabs, based on two criteria in column J and paste values
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-13-2013, 03:46 PM
  4. Replies: 0
    Last Post: 10-29-2013, 09:38 AM
  5. [SOLVED] Copy/Paste multiple columns as values based on another columns criteria
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2013, 06:50 AM
  6. [SOLVED] Macro to Copy Data and Paste Values Based on cell criteria
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-16-2012, 06:51 PM
  7. copy and paste values based on a criteria
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 11-15-2008, 11:35 AM

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