+ Reply to Thread
Results 1 to 20 of 20

How to Copy and paste different rows into another area based on certain criteria

  1. #1
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    How to Copy and paste different rows into another area based on certain criteria

    Table to be solved:
    Annual loan payments
    Months Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
    60 30 30 30 30 30 0 0 0 0 0
    84 20 20 20 20 20 20 20 0 0 0
    120 10 10 10 10 10 10 10 10 10 10

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    Where is the explanation? Table to be solved is not an explanation. You shouldn't expect us to help you without providing clear and concise details.
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    The problem is to select between months 60, 84 and 120 payments and thus the payment amounts as shown in each row in order to paste the correct payment amount and period into a cash flow sheet.

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    And how we should know what is the correct payment method and period to be pasted into a cash flow sheet if you provided some numbers "to be solved"? upload a sample and mock up the results in there, clearly explain the logic. To upload: Go Advanced - Manage Attachments -

  5. #5
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    A previous selection button will indicate the selected months and capital amount at play. The PMT function is used to calculate the payment values. It is not necessary to check on this detail - only how to copy and paste a selected row into another table/worksheet. Thus the data shown itself is just demo data.

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    If you already selected the row in, let's say, Sheet1 and you want it copied in Sheet2, next available row:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    I've uploaded the data table. It is not clear how the selection criteria was build into your suggestion. The paste section is at a specific position, say in row D15 of Sheet2 but with the same range.
    Attached Files Attached Files

  8. #8
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    Here is a macro that will search through the table the B1 value and copy paste the row to another sheet:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    How should the destination be changed so that the paste area is at a specific cell, say Sheet2! A15:J15

  10. #10
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    If you do not want the value to be copied in the next empty row and you want to replace the data in a single row, then change this
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    How should the code be changed not to include the months in the copied row, thus omitting 60, 84 and 120 months?

  12. #12
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    Please Login or Register  to view this content.
    like this

  13. #13
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    Perfect, thank you very much for your contribution. Case closed.

  14. #14
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    An unexpected error has surfaced. The code is working well with only values in the rows, but the moment any function or reference to another cell is introduced then only zeros are transferred. How can this be solved?

  15. #15
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    So, you may have formulas in your row and you want to copy only the values in the next sheet?

  16. #16
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    That is correct, only the values need to be transferred.

  17. #17
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    Untested:
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    I'm receiving an error: PasteSpecial method of range class failed at [.PasteSpecial Paste:=x1PasteFormat]

  19. #19
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to Copy and paste different rows into another area based on certain criteria

    Have you copied my code or written yourself? Cause is not
    Please Login or Register  to view this content.
    is
    Please Login or Register  to view this content.
    The red character is a lower L, not a 1

  20. #20
    Registered User
    Join Date
    09-23-2019
    Location
    Centurion, Pretoria
    MS-Off Ver
    Office 2013
    Posts
    11

    Re: How to Copy and paste different rows into another area based on certain criteria

    Thanks it is working correctly now.

+ 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 rows based on criteria
    By jeff p in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2014, 11:00 PM
  2. [SOLVED] Copy/Paste rows based on certain criteria (drop down or colors)
    By jholiday78 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-26-2014, 08:15 PM
  3. Copy and Paste rows to another spreedsheet based on multiple sets of criteria.
    By juhaszp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2013, 11:08 AM
  4. [SOLVED] copy and paste rows based on criteria in column
    By CharlieZangel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 12:54 PM
  5. Copy and Paste Rows based on Criteria onto another sheet and sort based on oldest item
    By Kushal8684 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2013, 05:37 AM
  6. [SOLVED] Macro to copy and paste rows to a new sheet based on criteria
    By Belo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2010, 02:25 AM
  7. Copy\Paste multiple criteria based rows to new sheet
    By namz in forum Excel Programming / VBA / Macros
    Replies: 46
    Last Post: 06-06-2008, 08:08 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