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
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
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.
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.
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 -
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.
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.
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.
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.
How should the destination be changed so that the paste area is at a specific cell, say Sheet2! A15:J15
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
toPlease Login or Register to view this content.
Please Login or Register to view this content.
How should the code be changed not to include the months in the copied row, thus omitting 60, 84 and 120 months?
like thisPlease Login or Register to view this content.
Perfect, thank you very much for your contribution. Case closed.
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?
So, you may have formulas in your row and you want to copy only the values in the next sheet?
That is correct, only the values need to be transferred.
Untested:
Please Login or Register to view this content.
I'm receiving an error: PasteSpecial method of range class failed at [.PasteSpecial Paste:=x1PasteFormat]
Have you copied my code or written yourself? Cause is notisPlease Login or Register to view this content.
The red character is a lower L, not a 1Please Login or Register to view this content.
Thanks it is working correctly now.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks