Copy/Paste Range Several Times while changing a few cells in 1-2 rows

Hi,

I am new on this forum, but I can already see it is a very useful resource for anyone who needs to learn how to do new stuff. So, hello and thanks for reading my thread.

I need to copy a range of data multiple times and make some changes to a few rows(2-3) in each set of copied data. Each row will be identified by the first cell. Example:

JobTitle Engineer 2 50-60k 2.5%


where Engineer is the job, 2 is the years at current job 50-60k is the salary range and 2.5% is the year-end bonus.

What I need to do is: copy the data set, search for rows that start with 'JobTitle' , modify 'Engineer' to 'Engineer 2 50-60' to denote job seniority and salary range. For this row, I will have dropdowns for job seniority 1-10+, salary range with different ranges and bonus percentages with different percentages. I wonder if it is possible to change "Engineer" to "Engineer SeniorityValue SalaryRange" and assign different values to 'SeniorityValue' and 'SalaryRange' as I change the values in the dropdowns.

The alternative would be having a prompt that asks me how many times I need the data copied and then asking for values for 'SeniorityValue' , 'SalaryRange' and 'Bonus' for each one of the copied data ranges from a predefined list of seniority years, salary ranges or bonus percentages. The rest of the copied data stays the same, it's just these rows that need to change. This would be the preferred solution, if it is not too complex?

I have the code to copy the data a predefined number of times, from this forum:

Please Login or Register  to view this content.

What I need is how to modify just 4 cells in 1-2 rows in each data set(those rows that start with 'JobTitle').

I hope I was clear enough, my knowledge of Excel is not very good.

Thanks.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Smiket
You shoudl help the readers to Help you.

.- Are those cells to be changed in some specific column, or they disperse through the entire range sh.Range("A2:DR68")
.- Is there a logic to fill the fields Years, Seniotity, Salary, Bonus , or it is only in your head

.- can you manage to put the dropdowns into Arrays(index , value )

After knowing that, using Find can bring you to a solution

I'm not sure to understand your requirements.
try the following code
assuming you have your dropdown list organized as the image in Sheet(2)

Captura.JPG

Please Login or Register  to view this content.