new sheet creation with specific information

Hi Swapijs

This code should get you started to create the sheets based on cell value

Option Explicit
Function getSheetWithDefault(name As String, Optional wb As Excel.Workbook) As Excel.Worksheet
        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If

        If Not sheetExists(name, wb) Then
            wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)).name = name
        End If

        Set getSheetWithDefault = wb.Sheets(name)
End Function

Function sheetExists(name As String, Optional wb As Excel.Workbook) As Boolean
        Dim sheet As Excel.Worksheet

        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If

        sheetExists = False
        For Each sheet In wb.Worksheets
            If sheet.name = name Then
                sheetExists = True
                Exit Function
            End If
        Next sheet
End Function


Sub CreateSheets()
        Dim MyCell As Range
        Dim MyRange As Range
        Dim ws As Worksheet

        Set MyRange = Sheets("Sheet1").Range("A2")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))

        For Each MyCell In MyRange
            If Sheets(Sheets.Count).name <> MyCell.Value Then
                Set ws = getSheetWithDefault(MyCell.Value)
            End If
        Next MyCell
    End Sub

This part copies the info to the required sheets

Option Explicit
Sub CopyInfo()
Dim wkSht As Worksheet
Dim cell As Range
Dim nextRow As Long
Dim lRow As Long
Dim i As Integer
lRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For Each wkSht In Sheets
    For i = 2 To lRow
        If Sheets("Sheet1").Range("A" & i).Value = wkSht.name Then
            wkSht.Activate
            nextRow = wkSht.Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheets("Sheet1").Range("A" & i).EntireRow.Copy Destination:=wkSht.Range("A" & nextRow)
        End If
    Next i
Next wkSht
Application.ScreenUpdating = True
End Sub

Don't understand this part:
2) in F column find specific names and in next sheet copy just value (for example if in column F it contains information like "Next business day" then in new sheet it just shows "N2", if Same business day, then "N1"
1. Copy value in what next sheet and what in new sheet

Here is a sample with the result from the above codes. Edit the file by showing a result of what you mean.

Hi Swapijs

This code includes the "N1" or "N2" column Populate

Option Explicit
Function getSheetWithDefault(name As String, Optional wb As Excel.Workbook) As Excel.Worksheet
        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If

        If Not sheetExists(name, wb) Then
            wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)).name = name
        End If

        Set getSheetWithDefault = wb.Sheets(name)
End Function

Function sheetExists(name As String, Optional wb As Excel.Workbook) As Boolean
        Dim sheet As Excel.Worksheet

        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If

        sheetExists = False
        For Each sheet In wb.Worksheets
            If sheet.name = name Then
                sheetExists = True
                Exit Function
            End If
        Next sheet
End Function

Sub CreateSheets()
        Dim MyCell As Range
        Dim MyRange As Range
        Dim ws As Worksheet

        Set MyRange = Sheets("Sheet1").Range("A2")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))

        For Each MyCell In MyRange
            If Sheets(Sheets.Count).name <> MyCell.Value Then
                Set ws = getSheetWithDefault(MyCell.Value)
            End If
        'Calls Subroutine to populate
        Next MyCell
        Call CopyInfo
    End Sub

Option Explicit
Sub CopyInfo()
Dim wkSht As Worksheet
Dim cell As Range
Dim nextRow As Long
Dim lRow As Long
Dim i As Integer
Dim celltxt As String
Dim celltxtval As String

lRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For Each wkSht In Sheets
Sheets("Sheet1").Activate
    For i = 2 To lRow
        celltxt = Sheets("Sheet1").Range("F" & i)
        If Sheets("Sheet1").Range("A" & i).Value = wkSht.name Then
            Sheets("Sheet1").Activate
            With Sheets("Sheet1")
                If LCase(InStr(1, celltxt, "Next business day")) Then
                    celltxtval = "N2"
                Else
                If LCase(InStr(1, celltxt, "Same business day")) Then
                    celltxtval = "N1"
                End If
                End If
            End With
            wkSht.Activate
            nextRow = wkSht.Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheets("Sheet1").Range("A" & i).EntireRow.Copy Destination:=wkSht.Range("A" & nextRow)
            wkSht.Range("L" & nextRow) = celltxtval
       End If
    Next i
Next wkSht
Application.ScreenUpdating = True
End Sub

I have no time to add the other code...will do so soonest.

Herewith the final code for your requirement...The subroutine Call CopyInfo. I am almost certain that there is a simpler way, however, I too am still learning lol.

Option Explicit
Sub CopyInfo()
Dim wkSht As Worksheet
Dim cell As Range
Dim nextRow As Long
Dim lRow As Long
Dim i As Integer
Dim celltxt As String
Dim celltxtval As String
Dim cellyearval As String

lRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For Each wkSht In Sheets
Sheets("Sheet1").Activate
    For i = 2 To lRow
        celltxt = Sheets("Sheet1").Range("F" & i)
        If Sheets("Sheet1").Range("A" & i).Value = wkSht.name Then
            Sheets("Sheet1").Activate
            With Sheets("Sheet1")
                If LCase(InStr(1, celltxt, "Next business day")) Then
                    celltxtval = "N2"
                Else
                If LCase(InStr(1, celltxt, "Same business day")) Then
                    celltxtval = "N1"
                End If
                End If
            End With
            With Sheets("Sheet1")
                If LCase(InStr(1, celltxt, "3 y")) Then
                    cellyearval = "3 years"
                    Else
                        If LCase(InStr(1, celltxt, "4 y")) Then
                            cellyearval = "4 years"
                            Else
                            If LCase(InStr(1, celltxt, "5 y")) Then
                                cellyearval = "5 years"
                            End If
                        End If
                 End If
            End With
                
            wkSht.Activate
            nextRow = wkSht.Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheets("Sheet1").Range("A" & i).EntireRow.Copy Destination:=wkSht.Range("A" & nextRow)
            wkSht.Range("L" & nextRow) = celltxtval
            wkSht.Range("M" & nextRow) = cellyearval
       End If
    Next i
Next wkSht
Application.ScreenUpdating = True
End Sub

Glad i could help. Thanks for the Rep points.

Hmmm, but there is some problem with it, when I copy on my original report file.
Do not understand, what is the problem.
I am so lost here, can not find anymore, how to add attachment

ok, so in attachment you can see, that original report is on first sheet, and then I create formated in sheet1 with my macros. And then I want to lunch your macros, but it is not working.

Hi Swapijs

Your above attached has no code.I have attached a updated version with the code.....I used Column Q & R to fill N1/N2 and 3Years/5Years etc

Open file
Enable Macros
Press [RUN MACRO]