Excel data need to separate to the new sheets

Copper Contributor

Hi Everyone,

I'm facing a challenge with my Excel data, and I'm seeking assistance in splitting the data from one header to another and copying it to a new sheet within the same Excel file. Here's a simplified example of what I have:

For example :



I appreciate any guidance or solutions you can provide to achieve this.

Thanks in advance! :)

1 Reply


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

    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.

data to new sheet.png