Cleaning and Consolidating Multiple Worksheets

VKS,
This code should work, but you need to have heading in each sheet as you now have in the first sheet. In sheet C, you also need to have values in column C, as you now have in sheet1. I have tested it in product sheet as you have all the data.

Sub Split_Sheet_Name1()
Dim i As Long, LR As Long, k As Long
Application.ScreenUpdating = False

On Error Resume Next
    
For i = 1 To Sheets.Count
 With Sheets(i)
        Sheets(i).Range("A2").Value = Trim(Split(Sheets(i).Name, "-")(0))
        Sheets(i).Range("B2").Value = Trim(Split(Sheets(i).Name, "-")(1))
    
        LR = .Cells(.Rows.Count, 3).End(xlUp).Row
        For k = 2 To LR
            If .Cells(k, 3).Value <> "" Then
                If .Cells(k, 2).Value = "" Then
                .Cells(k, 2).Value = .Cells(k - 1, 2).Value
                .Cells(k, 1).Value = .Cells(k - 1, 1).Value
                End If
            End If
        Next k
End With
Next i
Application.ScreenUpdating = True
End Sub