Excel data need to separate to the new sheets

charan405 

Sub Demo()
    
    Dim arrData, rngData As Range
    Dim i, j As Long
    
    i = 1
   
    For j = 1 To Range("I1").Value
     
    Sheets("Tabelle1").Activate
        
    Set rngData = Sheets("Tabelle1").Cells(Cells(i, 8), 1).CurrentRegion
    arrData = rngData.Value

    Sheets.Add
    Range("A1").Resize(UBound(arrData), 5).Value = arrData
    i = i + 1
    Next j
 
End Sub

In the attached file you can run this macro which copies each set of data to a new sheet. Before running the macro the number of required new sheets must be entered in cell I1. In cell I1 is a formula that returns the number of required new sheets. In column H are the row numbers from column A that contain the word "Application". There is a formula in cell H1 which returns these row numbers from column A. This formula is filled down.