I am trying to insert a row, say between rows 5 and 6 in my example data below and then copy the formulas up from row 7 (previously row 6) into the new row 6. I tried to figure out the code on my own by recording a macro but determined that my formulas are not correct for the desired result with my method of inserting and copying rows. As you will see below in my "Original Data" section, some cells reference the cell above (ie. cell 'C6' is '=C5'), so when I insert a row between rows 5 & 6, the new row 7 (old row 6) still references row 5. I need the new row 7 to reference the new row 6 and the new row 6 to reference row 5. The rows that are 2 rows below the newly inserted row update and are correct after the insertion. (I hope that makes sense)
I would greatly appreciate help writing VBA to achieve my desired result. Thank you in advance.
I would greatly appreciate help writing VBA to achieve my desired result. Thank you in advance.
Book1 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | ||||||||||||||||
2 | Original Data | What happens when I insert a new row in row 6 and copy row 7 up to row 6 | Desired result | |||||||||||||
3 | ||||||||||||||||
4 | Item No. | Length | Width | Ref. | Item No. | Length | Width | Ref. | Item No. | Length | Width | Ref. | ||||
5 | 1 | 10 | 20 | Blue | 1 | 10 | 20 | Blue | 1 | 10 | 20 | Blue | ||||
6 | 2 | 10 | 20 | Blue | #VALUE! | Length | Width | Ref. | 2 | 10 | 20 | Blue | ||||
7 | 3 | 10 | 20 | Red | 2 | 10 | 20 | Blue | 3 | 10 | 20 | Blue | ||||
8 | 4 | 12 | 20 | Blue | 3 | 10 | 20 | Red | 4 | 10 | 20 | Red | ||||
9 | 5 | 12 | 20 | Green | 4 | 12 | 20 | Blue | 5 | 12 | 20 | Blue | ||||
10 | 6 | 12 | 4 | Green | 5 | 12 | 20 | Green | 6 | 12 | 20 | Green | ||||
11 | 6 | 12 | 4 | Green | 7 | 12 | 4 | Green | ||||||||
Sheet1 |