Hi please refer to attached file. I have to copy data in cells A12 to A17 under heading Desired Variable (Column G). Only those cells should be copied which have status "Yes" in front of them. Similarly I have to copy formula in cells C12 to C17 under heading Desired Value (Column H) (based on the same condition "Yes"). Please suggest what code should I use.
Copying data based on condition
- Thread starter Brijesh
- Start date
Thanks Luke
I agree. But the actual aim is something different. Please refer to revised attached file. There is a list of all output variables in Column A on Sheet 1 and their values in Column C on Sheet 1. The values in column C depends on some other variable AGE. On sheet 2 there is a table in which all possible values of AGE is given in Column A. For each possible value of AGE, values of output variables on sheet 1 is to be generated in column C and then only those value are to be pasted on sheet 2 (in row of that AGE) for which status is "Yes". Row 8 in sheet 2 should have the name of variable from sheet 1 (for which status is "Yes"). For Example: For AGE =12 a set of values will be generated in column C on Sheet 1. Then those values which have status "Yes" will be pasted in row 9 on sheet 2 (transpose pasting). My initial idea was to first paste variable name and the variable value with status "Yes" in column G and H on sheet 1 itself. Then running a loop for all possible value of AGE and for each value of AGE, pasting values from column G and H on sheet 1 to rows (transpose pasting) on sheet2. Please can you suggest some method/vba coding?
Thanks
Brijesh
I agree. But the actual aim is something different. Please refer to revised attached file. There is a list of all output variables in Column A on Sheet 1 and their values in Column C on Sheet 1. The values in column C depends on some other variable AGE. On sheet 2 there is a table in which all possible values of AGE is given in Column A. For each possible value of AGE, values of output variables on sheet 1 is to be generated in column C and then only those value are to be pasted on sheet 2 (in row of that AGE) for which status is "Yes". Row 8 in sheet 2 should have the name of variable from sheet 1 (for which status is "Yes"). For Example: For AGE =12 a set of values will be generated in column C on Sheet 1. Then those values which have status "Yes" will be pasted in row 9 on sheet 2 (transpose pasting). My initial idea was to first paste variable name and the variable value with status "Yes" in column G and H on sheet 1 itself. Then running a loop for all possible value of AGE and for each value of AGE, pasting values from column G and H on sheet 1 to rows (transpose pasting) on sheet2. Please can you suggest some method/vba coding?
Thanks
Brijesh
Hi Luke,
The values in column C on sheet 1 depends on Age and actual relation of these values with Age is very complex. However for a prototype I have updated the excel file and the same is attached here. In this excel file please refer to sheet 1. Cell B6 has value for variable Age. Depending on this Age, values in C12 to C17 is calculated (you can see the formula in these cells). Now please go to sheet 2 on which there is a table. In this table Column A has possible values for Age (12 to 27). The task is to pick the values of Age one by one. For Example, lets first pick the value 12 for Age which is in row 9. Put this value of Age (12) in Cell B6 on sheet 1. Then some values will be generated in C12-C17 (on Sheet 1). Then I have to copy only those values for which status is "Yes" (In this case I have to copy values in Cells C12, C13, C15 and C17). Then I have to paste these values in row 9 in table on Sheet 2 i.e. in front of Age 12. Then this whole process is to be repeated for all values of Age (i.e. from Age =12 to Age =27). Note that the status (Yes/No) in Column B on sheet 1 is to be filled by user and hence can be changed by user.
Since I have to repeat the process for Age =12 to Age = 27, I thought that for any value of Age, whatever value is generated in Cells C12-C17 on sheet 1, I should copy those value which have status "Yes" in column H under heading Desired Value on Sheet 1. Then I can copy this entire range under heading Desired Value and paste (transpose) in table on sheet2.
Please let me know that whether this explains what I want? If yes, please suggest the method/code
Thanks
Brijesh
Put this value in Cell B6 on sheet 1. Then for that value of age some values will be generated in C12 to C17 (on sheet 1). Next thing is to copy those values from C12-C17 for which status is "Yes" and then paste it to the row in t
The values in column C on sheet 1 depends on Age and actual relation of these values with Age is very complex. However for a prototype I have updated the excel file and the same is attached here. In this excel file please refer to sheet 1. Cell B6 has value for variable Age. Depending on this Age, values in C12 to C17 is calculated (you can see the formula in these cells). Now please go to sheet 2 on which there is a table. In this table Column A has possible values for Age (12 to 27). The task is to pick the values of Age one by one. For Example, lets first pick the value 12 for Age which is in row 9. Put this value of Age (12) in Cell B6 on sheet 1. Then some values will be generated in C12-C17 (on Sheet 1). Then I have to copy only those values for which status is "Yes" (In this case I have to copy values in Cells C12, C13, C15 and C17). Then I have to paste these values in row 9 in table on Sheet 2 i.e. in front of Age 12. Then this whole process is to be repeated for all values of Age (i.e. from Age =12 to Age =27). Note that the status (Yes/No) in Column B on sheet 1 is to be filled by user and hence can be changed by user.
Since I have to repeat the process for Age =12 to Age = 27, I thought that for any value of Age, whatever value is generated in Cells C12-C17 on sheet 1, I should copy those value which have status "Yes" in column H under heading Desired Value on Sheet 1. Then I can copy this entire range under heading Desired Value and paste (transpose) in table on sheet2.
Please let me know that whether this explains what I want? If yes, please suggest the method/code
Thanks
Brijesh
Put this value in Cell B6 on sheet 1. Then for that value of age some values will be generated in C12 to C17 (on sheet 1). Next thing is to copy those values from C12-C17 for which status is "Yes" and then paste it to the row in t
Hi Luke,
Thanks for great help. Please can you let me know the code to copy column A and column C (i.e A12-A17 and C12-C17) based on "Yes" criteria and then paste Column A values in Column G and Column C formulas in Column H (Column H should contain the same formula as it is in column C). The other things like looping for Age I'll do as per my requirements. I am attaching my original file.
Thanks
Brijesh
Thanks for great help. Please can you let me know the code to copy column A and column C (i.e A12-A17 and C12-C17) based on "Yes" criteria and then paste Column A values in Column G and Column C formulas in Column H (Column H should contain the same formula as it is in column C). The other things like looping for Age I'll do as per my requirements. I am attaching my original file.
Thanks
Brijesh
Hi Brijesh,
Did you even try my file? I asked you for what you wanted the numbers to look like. You didn't fill out Sheet2 as requested, but instead left it blank. The workbook you just posted is the same as before, so I still have no new information to work with. YOu described that you wanted the values copied over from Sheet1 to Sheet2 if the Status is 'Yes'. That is what I have given you.
Macros have to be written with very specific instructions telling the code where to go. When you give a sheet that either isn't truly reprsentative of what your actual workbook looks like, or change things around, it just wastes coders' time and makes them a bit frustrated.
As I said, the solution I just posted will generate all the values and copy them over. In fact, I gave you two solutions. I suggest taking a second look at my most recent solution proposed, run the macro, and see if it does not indeed solve your question. If not, you will need to CLEARLY state what is wrong, and it would be even more helpful if you posted the example workbook with the data filled in how you want it to appear. If you post the same blank workbook again, no one will be able to help.
Did you even try my file? I asked you for what you wanted the numbers to look like. You didn't fill out Sheet2 as requested, but instead left it blank. The workbook you just posted is the same as before, so I still have no new information to work with. YOu described that you wanted the values copied over from Sheet1 to Sheet2 if the Status is 'Yes'. That is what I have given you.
Macros have to be written with very specific instructions telling the code where to go. When you give a sheet that either isn't truly reprsentative of what your actual workbook looks like, or change things around, it just wastes coders' time and makes them a bit frustrated.
As I said, the solution I just posted will generate all the values and copy them over. In fact, I gave you two solutions. I suggest taking a second look at my most recent solution proposed, run the macro, and see if it does not indeed solve your question. If not, you will need to CLEARLY state what is wrong, and it would be even more helpful if you posted the example workbook with the data filled in how you want it to appear. If you post the same blank workbook again, no one will be able to help.
Hi Luke,
I agree with you. I know macros have to be written with very specific instructions telling the code where to go.
Actual workbook has 25 interdependent worksheets. Hence it's not possible to create similar sheet and at the same time I cannot share actual workbook as it contains very confidential company data. I will try to decode your coding and recode to fit to my actual workbook as much as possible.
May be that I could not clearly specify the things which wasted your time and frustrated you. Apologies...
Thanks for your great help
Brijesh
I agree with you. I know macros have to be written with very specific instructions telling the code where to go.
Actual workbook has 25 interdependent worksheets. Hence it's not possible to create similar sheet and at the same time I cannot share actual workbook as it contains very confidential company data. I will try to decode your coding and recode to fit to my actual workbook as much as possible.
May be that I could not clearly specify the things which wasted your time and frustrated you. Apologies...
Thanks for your great help
Brijesh