Row addition macro: How to also copy what's above?

The following formula adds a row for me when a certain cell has a 4. If it has 5-8 it adds 2 rows. If the values is 9-12 it adds 3 rows.

PHP Code: 

Sub Row_Addition() 'This adds rows If I have more than 4 pictures per Pole
    Dim i As Long
    Sheets("Checksheet").Activate
    For i = 79 To 5 Step -1
        Debug.Print i
        Select Case Cells(i, "N").Value
            Case 5 To 8
                Cells(i + 1, "N").EntireRow.Insert
            Case 9
                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert
            Case 10 To 12
                Cells(i + 1, "N").EntireRow.Insert
            Case 13
                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert
        End Select
    Next i
            
End Sub 


What I don't know how to do is, 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.

Maybe this can help?

Sub JPWRana() 'This adds rows If I have more than 4 pictures per Pole
    Dim i As Long
    Sheets("Checksheet").Activate
    For i = 79 To 5 Step -1
        Debug.Print i
        Select Case Cells(i, "N").Value
            Case 5 To 8
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case 9
                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert
                Cells(i, "N").EntireRow.Copy Range(Cells(i + 1, "N"), Cells(i + 2, "N"))
            Case 10 To 12
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case 13
                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert
                Cells(i, "N").EntireRow.Copy Range(Cells(i + 1, "N"), Cells(i + 3, "N"))
        End Select
    Next i
            
End Sub

For the new rows.

I got an error at the following line:

Cells(i, "N").EntireRow.Copy Range(Cells(i + 1, "N"), Cells(i + 2, "N"))

Can you attach a sample for testing? To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

The macro applied to only 3 different poles in this case.

Pole 27 required 2 row additions (There is a 9 on N31). ON THAT ROW I got an error.

On Poles 52 & 55, only 1 row was needed (N63 & N9 had a value greater than 4 but less than 9).


I highlighted in YELLOW how the macro gave me the results.
I copied those rows just above it and highlighted it in GREEN to show how it needs to look like once the macro does its work.

Note: Columns O - R have that long formula where if I want the row addition (which applied only 3 times in this case), the only change that is needed is the Sheet1'!J## part of it (## = a number). I made that change manually to show how those row addition formulas need to be.

Let me know what you think.

Let me know if this works for points 1-3? If so, I'll try to modify for points 4 and 5.

Sub JPWRana() 'This adds rows If I have more than 4 pictures per Pole
    Dim i As Long
    Sheets("Checksheet").Activate
    For i = 79 To 5 Step -1
        Debug.Print i
        Select Case Cells(i, "N").Value
            Case 5 To 8
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case 9
                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 2, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case 10 To 12
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case 13
                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 2, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 3, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
            Case Else
                GoTo zz
        End Select
zz:
    Next i
            
End Sub

John, is this supposed to do requests 1) through 5)?

I think this only handles 1-3. I'll look at 4-5 in the morn., if no one else helps. I'm going off line now.

Sure. Let me clean it up a bit then I'll upload it.

Meanwhile, in seeing how the macro ran, it did only 1). It copied everything over when I didn't want to. It copied over A2, L2 and M2, where I didn't want it to.

John, there is mixed success with the updated macro.

1) I no longer get errors
2) Cell ?2 does copy over for 2 and 3 rows.
3) Cell N? does copy over for rows 2 and 3.

What it also does that I don't want it to do is...
1) Copy A? down onto the next cells. It's not a deal breaker but it does throw me off.
2) Copies L? and M? also without issue, but I also don't want that copied.


What it still doesn't do
1) Give me a merged C? with D? (? = same number)
2) The modified formulas from columns O-R.

See my uploaded excel file from post #8 to see how they need to look like.

Thanks again for your help.

Hard to do with the links.

Maybe:

Sub JPWRana() 'This adds rows If I have more than 4 pictures per Pole
    Dim i As Long
    Sheets("Checksheet").Activate
    For i = 79 To 5 Step -1
        Debug.Print i
        Select Case Cells(i, "N").Value
            Case 5 To 8
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 1, "A").ClearContents
                Range(Cells(i + 1, "L"), Cells(i + 1, "M")).ClearContents
                Range(Cells(i + 1, "C"), Cells(i + 1, "D")).Merge
                Cells(i, "N").Offset(, 1).Resize(, 3).Copy Cells(i + 1, "O")
            Case 9
                Range(Cells(i + 1, "N"), Cells(i + 2, "N")).EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 2, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Range(Cells(i + 1, "A"), Cells(i + 2, "A")).ClearContents
                Range(Cells(i + 1, "L"), Cells(i + 2, "M")).ClearContents
                Range(Cells(i + 1, "C"), Cells(i + 1, "D")).Merge
                Range(Cells(i + 2, "C"), Cells(i + 2, "D")).Merge
                Cells(i, "N").Offset(, 1).Resize(, 3).Copy Cells(i + 1, "O")
                Cells(i, "N").Offset(, 1).Resize(, 3).Copy Cells(i + 2, "O")
            Case 10 To 12
                Cells(i + 1, "N").EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 1, "A").ClearContents
                Range(Cells(i + 1, "L"), Cells(i + 1, "M")).ClearContents
                Range(Cells(i + 1, "C"), Cells(i + 1, "D")).Merge
                Cells(i, "N").Offset(, 1).Resize(, 3).Copy Cells(i + 1, "O")
            Case 13
                Range(Cells(i + 1, "N"), Cells(i + 3, "N")).EntireRow.Insert
                Cells(i + 1, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 2, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Cells(i + 3, "N").EntireRow.Value = Cells(i, "N").EntireRow.Value
                Range(Cells(i + 1, "A"), Cells(i + 3, "A")).ClearContents
                Range(Cells(i + 1, "L"), Cells(i + 3, "M")).ClearContents
                Range(Cells(i + 1, "C"), Cells(i + 1, "D")).Merge
                Range(Cells(i + 2, "C"), Cells(i + 2, "D")).Merge
                Range(Cells(i + 3, "C"), Cells(i + 3, "D")).Merge
                Cells(i, "N").Offset(, 1).Resize(, 3).Copy Range(Cells(i + 1, "O"), Cells(i + 3, "O"))
            Case Else
                GoTo zz
        End Select
zz:
    Next i
            
End Sub

Yes, you are right, the new macro does pick up Column R, and you are also right, if I add a $ sign it does make it fixed.

However, I only need it to be fixed when I copy O-R to the new row (or 2 or 3), so that the new formulas on the new cells from the new rows on O-R pick up Sheet1!J28. Other than that, Sheet1!J28 isn't supposed to be Sheet1!$J$28="".

Do I need to approach the formula entirely a different way? Or can the macro handle a tweak in formulas O-R ONLY WHEN ROWS ARE BEING ADDED?

That's a tough one. I feel you'd have to adjust the formula vrs the macro. I'm a bit afraid to recommend another if statement too such a complex formula, and I'm not well versed in formulas. That's all I can think of?