Any idea on how to create a row at a change in value and copy cells from the row above it?

[dustinb]

I've searched on Google for hours trying to find an answer to this and for the most part I've come up with some code that does a good job of creating a row after each change in value and copying the row above it. But what I wanted to know was how to change individual cell data within that duplicated row.

So for example:

	A	B	C	D
1	ball	cat	dog	elephant
2	ball	giraffe	zebra	apple
3	ball	fruit	wood	ape
4	pumpkin	apple	pen	pencil
5	pumpkin	orange	lemon	grape
6	monkey	keyboard	mouse	can
7	monkey	llama	chestnuts	pill

Looking just at Column A you'll see that there is similar data in groups of rows. I need to create a row after each change in value (so new rows would be created between 3 and 4, 5 and 6, and after 7), the new row would need to COPY everything in in the row immediately above it, and then I'm lost as to what to do next which is editing specific cells to display different data. Here's the code I'm working with:

Please Login or Register  to view this content.

This code finds the change in values in Column CC (in my spreadsheet, not pertaining to the example I've given), adds a new row after each change in value, and copies the row above it. I just need to alter some data in the cells after everything is copied. So going back to the example this is what would happen with the code I have now:

	A	B	C	D
1	ball	cat	dog	elephant
2	ball	giraffe	zebra	apple
3	ball	fruit	wood	ape
4	ball	fruit	wood	ape
5	pumpkin	apple	pen	pencil
6	pumpkin	orange	lemon	grape
7	pumpkin	orange	lemon	grape
8	monkey	keyboard	mouse	can
9	monkey	llama	chestnuts	pill
10	monkey	llama	chestnuts	pill

Rows 4, 7, and 10 have been added and each copied the row directly above it. The reason I need to do this is because each new line in my spreadsheet is supposed to contain most of the same data as the group of rows above it, just with some minor tweaks. So I thought copying the row and THEN changing data would be the best way to go about it.

So what I need now is to figure out a way to get this code to change values in column B ONLY in the new rows added (not to the whole spreadsheet) to say "cherries" or something, like this:

	A	B	C	D
1	ball	cat	dog	elephant
2	ball	giraffe	zebra	apple
3	ball	fruit	wood	ape
4	ball	cherries	wood	ape

You'll see that in row 4 the code added a new row after the change in value in column A (ball), copied everything in the row above it (row 3), and THEN changed the value in column B just in that row to display cherries.

I need to do this for multiple cells as well. I have close to 250 columns and I need the code to change the values in multiple cells after copying the row above it, not just one.

Hopefully that makes some sense. Thanks for any help!

[arlu1201]

Ok inserting rows, copying the above row is fine.

What should be the text to be entered in column B of the newly added rows? You said cherries should be entered for the 1st example, what about for the other rows?

[dustinb]

Actually, I just needed all the newly added rows to display cherries in column B, so row 7 and row 10 would also display "cherries" in Column B, as well. That's why I just showed the first example. So what I need is some code that will tell every row added to copy the row above it with exception of all cells in Column B displaying text that I tell it to display (cherries, in this case).

And what I meant by needing to do this in multiple cells is to, for example, tell it to display "cherries" in Column B, "dog" in Column D, "tree" in Column AA, etc.

So the whole problem is basically adding a new row at a change in value, copying everything from the row above, then telling cells in certain columns to print something different that I would indicate.

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button