How can I create new sheet in one excel sheet from data from another excel sheet

You could probably do something using advanced/auto filter.

However it's hard to give specific advice/code without seeing the data and those are hugh files you've uploaded.

Could you upload smaller example files?

PS If you really have the amount of data  the file size seems to indicate it might be worth transferring it to a database.

Try this,

Option Explicit

Sub SplitToSheets()
Dim wbNew As Workbook
Dim wsIn As Worksheet, wsOut As Worksheet
Dim rwMax As Long, colMax As Integer
Dim rwRes As Long, rw As Long
Dim arList() As Variant, arRes() As String

Const colDescriptor As Integer = 3
    
    Application.ScreenUpdating = False
    Set wsIn = ThisWorkbook.Worksheets("Sheet1")
    wsIn.Select
    If wsIn.AutoFilterMode = True Then
        wsIn.AutoFilterMode = False
    End If
    wsIn.Range("A1").Select
    Selection.AutoFilter
    rwMax = wsIn.Range("A1").CurrentRegion.Rows.Count
    colMax = wsIn.Range("A1").CurrentRegion.Columns.Count
    arList = wsIn.Range(Cells(2, colDescriptor), Cells(rwMax, colDescriptor))
    arList = SortList(arList, True)
    rwRes = 1
    For rw = 2 To UBound(arList)
        If arList(rw, 1) <> arList(rw - 1, 1) Then
            rwRes = rwRes + 1
        End If
    Next rw
    ReDim arRes(1 To rwRes)
    arRes(1) = arList(1, 1)
    rwRes = 1
    For rw = 2 To UBound(arList)
        If arList(rw, 1) <> arList(rw - 1, 1) Then
            rwRes = rwRes + 1
            arRes(rwRes) = arList(rw, 1)
        End If
    Next rw

    For rwRes = 1 To UBound(arRes)
        Set wsOut = Worksheets.Add
        wsOut.Name = CleanName(arRes(rwRes))
        wsIn.Select
        wsIn.Range(Cells(1, 1), Cells(rwMax, colMax)).AutoFilter Field:=colDescriptor, Criteria1:=arRes(rwRes)
        wsIn.Range(Cells(1, 1), Cells(rwMax, colMax)).Copy
        wsOut.Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        wsOut.Columns.AutoFit
        wsOut.Range("A1").Select
        If rwRes = 1 Then
            wsOut.Move
            Set wbNew = ActiveWorkbook
        Else
            wsOut.Move After:=wbNew.Worksheets(CleanName(arRes(rwRes - 1)))
        End If
        ThisWorkbook.Activate
        wsIn.Select
    Next rwRes
    wsIn.AutoFilterMode = False
End Sub

Function SortList(ByVal List As Variant, ByVal Ascending As Boolean) As Variant
Dim i As Long, j As Long, Temp As Variant, Max As Long, Min As Long
    Max = UBound(List)
    For j = 1 To Max
        Min = j
        For i = j + 1 To Max
            If List(i, 1) > List(Min, 1) Xor Ascending Then Min = i
        Next i
        If Min <> j Then
            Temp = List(j, 1)
            List(j, 1) = List(Min, 1)
            List(Min, 1) = Temp
        End If
    Next j
    SortList = List
End Function

Function CleanName(txt As String) As String
    CleanName = txt
    If InStr(1, CleanName, ":") > 0 Then CleanName = Replace(CleanName, ":", " ")
    If InStr(1, CleanName, "\") > 0 Then CleanName = Replace(CleanName, "\", " ")
    If InStr(1, CleanName, "/") > 0 Then CleanName = Replace(CleanName, "/", " ")
    If InStr(1, CleanName, "[") > 0 Then CleanName = Replace(CleanName, "[", " ")
    If InStr(1, CleanName, "]") > 0 Then CleanName = Replace(CleanName, "]", " ")
    If InStr(1, CleanName, Chr(63)) > 0 Then CleanName = Replace(CleanName, Chr(63), " ") '?
    If InStr(1, CleanName, Chr(42)) > 0 Then CleanName = Replace(CleanName, Chr(42), " ") '*
    CleanName = Left(CleanName, 31)
End Function

Select allOpen in new window

Good but I need the new Excel to be an existing one note new. like the name should be all the time Excel-2 and the path is fixed.

I don't understand your comments.
The macro creates a new file with a sheet for each different discriptor just like 2-new.
What do you mean with

like the name should be all the time Excel-2 and the path is fixed.

My sheet "Excel-2" has some macros and I want to update it with the new data coming from Excel-1 without creating the workbook "Excel-2" from scratch. If you couldn't overwrite the sheets in  Excel-2, you could delete them and create them again. So Excel-2 is already exist with a specif path like "D:\Excel-2_final.xlmx" and I want just to create the new sheets inside Excel-2.

Regards,

Dallag

wow thank you so much for your help

Dallag

Can I also read from another existing file. I don't to copy the macro to source file.

Regards,

Dallag

Sure, you need to have a blank workbook with the macro, and then open the 2 workbooks, just like the one just send opening Excel-2.
I will look at it tomorrow, it is late now here.

please help me now. I need it urgent

How can I use this. I found all XML files. What should I do to work with theses files?

Regards,

Dallag

When you press the button to run, the program ask for the 2 Excel files to use.
See the header in the dialog.
First the file with the new data, "Open file to copy data from".
Then the file with the existing data, to insert the new data in, "Open file to copy sheets to".

The new data in file 1, must be on Sheet1.
If not, the reference must be changed in the program.


XML files?
There is a button for Import on the Developer tab.
Use that and save it as an Excel file if the data looks like the sample Excel file "1-new.xlsx"

It should be possible to make the macro import xml.
Upload a sample, and I will test.

Could you tell me how can I got the button? What I should do with the zip file you sent?

Thank you it is OK now :)

Regards,

Dallag