getting data from sheet to multiple sheets before TOTAL row

Alaa

In the revised file attached, new Module 2 has a new sub to clear existing data on sheets (other than MAIN). The code just loops through sheets and deletes any rws betwen 17 and the TOTAL row (I've added some duplicated data in tbefile as it leaves me, so you can see it work within the main macro):

Sub ClearData()

For i = 1 To Sheets.Count
    If Sheets(i).Name <> "MAIN" Then
        With Sheets(i)
            B4Tot = .Range("C" & .Rows.Count).End(xlUp).Row - 1
            ' delete any rows between 17 and row with "Total" in C
            If B4Tot >= 17 Then .Rows("17:" & B4Tot).EntireRow.Delete
        End With
    End If

Next i

End Sub

This gets called in the revised code below which adds new row (with the fomat of existing data rows above) and also writes the formula in cells added in columns E and F of each sheet. It's commented so you can see what's happening; key bits in bold:

Sub getting_values2()

Dim cl As Range, i As Long
Dim MaLstRw As Long, B4Tot As Long

Set WS = ThisWorkbook.Sheets("MAIN")

' clear data any data rows in sub-sheets
Call ClearData

MaLstRw = WS.Range("A" & Rows.Count).End(xlUp).Row
' loop down main sheet
For Each cl In WS.Range("A12:A" & MaLstRw)
    ' check non-zero entries
    If cl.Value <> "" Then
        'find matching sheet
        For i = 1 To Sheets.Count
            With Sheets(i)
                If Sheets(i).Name = cl.Value Then
                ' when found, find last row of that sheet
                    B4Tot = .Range("C" & .Rows.Count).End(xlUp).Row
                    ' insert new row, copying format from above
                    .Rows(B4Tot).EntireRow.Insert Shift:=xlDown, _
                        CopyOrigin:=xlFormatFromLeftOrAbove
                    'copy values from MAIN
                    .Range(.Cells(B4Tot, 1), .Cells(B4Tot, 5)).Value = cl.Offset(0, 2).Resize(1, 5).Value
                    ' write formula in column E
                    .Cells(B4Tot, 6).Formula = "=F" & B4Tot - 1 & " + E" & B4Tot & " - D" & B4Tot
                    ' write formula in column F
                    .Cells(B4Tot, 7).Formula = "=D" & B4Tot & " + E" & B4Tot
                    ' add totals in row below
                    .Cells(B4Tot + 1, 4).Formula = "=SUM(D15:D" & B4Tot & ")"
                    .Cells(B4Tot + 1, 5).Formula = "=SUM(E15:E" & B4Tot & ")"
                End If
            End With
        Next i
    End If
Next cl

MsgBox "Data replaced in sub-sheets"

End Sub

I note that you have about 280 odd rows in MAIN with no data in column A (but the sub skips over those).

Hope this fixes things for you.