Macro Help. How to add row when certain value reached?

[JPWRana]

I need help with a macro that adds a row under the following condition:

From N5:N80, if any of those cells contains a 5 or higher, then add a row below that cell where you have the 5 or higher?

Ex: N26 has a value of 6. Therefore I would want a row below N26. Then N55 has a 7. I want a row added below N55?

If cell value is 5, add 1 row. If cell value is 9, add 2 rows. If cell value is 13 add 3 rows. That's it.

Is this possible?

 

[JPWRana]

I put in the row addition macro to a different area because I copied and pasted with my Hyperlink Macro, and then I made a "Call Row_Addition" to the Hyperlink macro, but it won't work.

It will only work if I run the macro separately... AFTER the Hyperlink Macro is complete, which is weird because I Call the Row_Addition macro that you made once the Hyperlink Macro is done. Why is that?

 

[Smitty]

I don't see why Call won't work.

 

[JPWRana]

I don't know if it matters or not but...

When I run my first macro (creating list of hyperlinks on A:A)
The resulting list is used to run other formulas to get where I am now.
The macro you gave me will run, but ONLY afterwards, which is weird because I call up your macro once my hyperlink macro is done.

Any idea why this would be?

The number that determines row addition comes from a formula. I hope any of this matters.

 

[Smitty]

It's possible that the code is trying to run before your formulas have recalculated.

 

[JPWRana]

I think I may have an idea why it won't figure it out.

My macro runs on Sheet 1.
Your macro is supposed to run on Sheet "Checksheet".

Does this make a difference?

 

[Smitty]

Then you need to explicitly reference Checksheet in the code that I posted, otherwise it will assume that you want it to run on the active sheet.

 

[JPWRana]

It did work. I had to reference the right tab. I did forget to ask though...

When I insert the rows, how can I have the macro (in addition to adding the rows under those conditions)

1. Copy exactly what's above B? (? is the row number just above the new row addition)
2. Copy exactly what's on row C? (Row C is merged with for example C1 and D1)
3. Now here is the REAL TRICKY ONE...

Ahhh, I'll wait... I need time just to even know how to explain that scenario.

 

[Smitty]

For 1 & 2 I'd look at using Offset to go to the range you want to copy.

I'd also post the code that works now and indicate where you want the copy/paste to occur.

 

[JPWRana]

how can I add in the same macro...

1) If a row is being added, how can I also add on the new row, exactly what is above ?2 (?= Column Letter)
2) If 2 rows are being added, how can I add to those 2 new rows exactly what is above ?2 (?= Column Letter)
3) If 3 rows... etc.

4) Also, C5 and D5 are merged. How can I (in this macro) also tell the row addition macro to merge ?5 & (?+1)5?

5) I have a set of formulas on O5, P5, Q5, and R5. When i add rows, I also need the rows to be populated where usually by hand you highlight then, and drag on the bottom right hand corner... EXCEPT that when they get added where the new rows are added (due to the macro), a slight change needs to happen...

This is formula for O5

=IF(Sheet1!J2="","",IF(AND($B5=$B4,$B4=$B3,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4,$N3)&")"&".JPG","("&SUM(O$4,$N4,$N3)&")"),IF(AND($B5=$B4,$N5>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B4&"("&SUM(O$4,$N4)&")"&".JPG","("&SUM(O$4,$N4)&")"),IF($N5>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"(1).JPG","(1)"),""))))

If there will be a row addition (a new Row 6), then O6 must be
=IF(Sheet1!J3="","",IF(AND($B6=$B5,$B5=$B4,$N6>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"("&SUM(O$4,$N5,$N4)&")"&".JPG","("&SUM(O$4,$N5,$N4)&")"),IF(AND($B6=$B5,$N6>=1),HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B5&"("&SUM(O$4,$N5)&")"&".JPG","("&SUM(O$4,$N5)&")"),IF($N6>=1,HYPERLINK($U$2&$G$1&"\"&$D$2&"\"&$B6&"(1).JPG","(1)"),""))))

Which is similar if you just highlight and drag down, EXCEPT THAT Sheet1!J3 MUST SAY Sheet1!J2. Everything else is good.

Any help is greatly appreciated.