Split all cells before lines that contains a specific string and create new rows.

[topi1]

Is it possible to have a vba which looks at a column, and then adds rows before every line that contains a specific string, in my example "shows". Everything else is kept in the same row unless a new line with the specific string occurs. Thank you. I have looked hard about adding rows above and below. I have found ways with single words and single occurrences but not for anything like I am hoping for. Thank you for everyone's help here.

Before

Book1
	A
1	Page 1 shows a large picture. Picture is of a baby. Page 2 shows a cartoon. It is from far side. Page 3 shows today's news.
2
3	Page 4 shows sports. Mostly NFL. Page 5 shows ads.
Sheet1

After

Book1
	A
1	Page 1 shows a large picture. Picture is of a baby.
2
3	Page 2 shows a cartoon. It is from far side.
4
5	Page 3 shows today's news.
6
7	Page 4 shows sports. Mostly NFL.
8
9	Page 5 shows ads.
Sheet2

 

[topi1]

ah ok.


Try:

Sub split_line()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
  Dim s As Variant
  Dim i As Long, ini As Long
  Dim myStr As String
 
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  ini = 1
  myStr = "shows"
 
 
  i = ini
  For Each c In sh1.Range("A1", sh1.Range("A" & Rows.Count).End(3))
    If InStr(1, c.Value, myStr, vbTextCompare) = 0 And c.Value <> "" Then
      sh2.Range("A" & i).Value = c.Value
      i = i + 2
    Else
   
      For Each s In Split(c.Value, ".", , vbTextCompare)
        If Trim(s) <> "" Then
          If InStr(1, s, myStr, vbTextCompare) > 0 Then
            If sh2.Range("A" & i).Value = "" Then
              sh2.Range("A" & i).Value = Trim(s) & "."
            Else
              sh2.Range("A" & i).Value = sh2.Range("A" & i).Value & s & "."
            End If
            i = i + 2
          Else
            If i = ini Then
              sh2.Range("A" & ini).Value = sh2.Range("A" & ini).Value & s & "."
            Else
              sh2.Range("A" & i - 2).Value = sh2.Range("A" & i - 2).Value & s & "."
            End If
          End If
        End If
      Next
    End If
  Next
End Sub

@DanteAmor
MAGIC! Brilliant! Thank you, Sir! Much appreciated!