Split cell data into multiple new rows and copy other column values

[jooga]

Hi,

I have a spreadsheet that has formula copying specific information from sheet 1 to other sheets. If the cell value in those other sheets has more than 1 value separated by an ALT Enter, then I need to create new rows, one for each value separated with an ALT Enter and the values from other columns repeated.

I have attached the spreadsheet where 4 sheets are copying over the information from sheet 1. Sheet "process Role to Process" has a macro against it as below, however it seems to be adding three new rows but splitting the data over existing rows and not copying over remaining values.

I would really appreciate any help asap please as I am not getting anywhere fast.

Thanks in advance
Sal.

Private Sub Worksheet_Activate()
 Dim varItm As Variant
    Dim rngText As Range
    Dim rngCl As Range
    Dim i As Integer
    Dim j As Integer

    Set rngText = Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row)
    j = 1
    For Each rngCl In rngText
        arrText = Split(rngCl, Chr(10))
        i = 2
        For Each varItm In arrText
            Range("C5").EntireRow.Insert
            i = i + 1
        Next varItm
        j = j + 1
    Next rngCl
    
        j = 1
    For Each rngCl In rngText
        arrText = Split(rngCl, Chr(10))
        i = 2
        For Each varItm In arrText
            Cells(i, j) = varItm
            i = i + 1
        Next varItm
        j = j + 1
    Next rngCl
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub