Forum Discussion

charan405's avatar
charan405
Copper Contributor
Dec 18, 2023

Excel data need to separate to the new sheets

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! 🙂






excel
Formulas and Functions
Macros and VBA
office 365

1 Reply

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 18, 2023

    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.

Resources