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

How can I create new sheet in one excel file from data from another excel file. The two attached files 1 and 2 are attached here. I need to create the sheet "CHOKE VALVE D S PRESSURE" in MS Excel file 2 from the data in the excel file 1. Only the "CHOKE VALVE U/S PRESSURE" from the column descriptor should be copied in the sheet name "CHOKE VALVE U S PRESSURE". The sheet name and data should be created automatically based on the different names in the column descriptor in the file 1. I just made one example manually.

Your urgent help is highly appreciated.

Regards,

Dallag
2.xlsm
1.xlsx
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
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.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
I made the files so small and I attached them again.

How can I create new sheet in one excel file from data from another excel file. The two attached files 1-new and 2-new are attached here. I need to create the sheet "CHOKE VALVE D S PRESSURE" in MS Excel file 2-new from the data in the excel file 1-new. Only the "CHOKE VALVE U/S PRESSURE" from the column descriptor should be copied in the sheet name "CHOKE VALVE U S PRESSURE". The sheet name and data should be created automatically based on the different names in the column descriptor in the file 1-new. I just made one example manually.

Your urgent help is highly appreciated.

Regards,

Dallag
1-new.xlsx
2-new.xlsx
0
Ejgil HedegaardCommented:
Try this, see sample file based on 1-new.xlsx

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

Open in new window

Split-to-new-workbook.xlsm
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Mohammed DallagPetroleum ConsultantAuthor Commented:
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.
0
Ejgil HedegaardCommented:
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.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
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
0
Ejgil HedegaardCommented:
In attached workbook, you can select the file to copy sheets to.
If you have a fixed path, and don't want to select, replace Application.GetOpenFilename....
with the path and filename.
If a sheet in Excel-2 have the same name the sheet is deleted and the new inserted.
New sheets are inserted left to existing sheets.
Split-to-existing-workbook.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammed DallagPetroleum ConsultantAuthor Commented:
wow thank you so much for your help

Dallag
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Can I also read from another existing file. I don't to copy the macro to source file.

Regards,

Dallag
0
Ejgil HedegaardCommented:
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.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
please help me now. I need it urgent
0
Ejgil HedegaardCommented:
Sorry, I closed down, but here it is.
Split-data-from-one-workbook-to-another-
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
How can I use this. I found all XML files. What should I do to work with theses files?

Regards,

Dallag
0
Ejgil HedegaardCommented:
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.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Could you tell me how can I got the button? What I should do with the zip file you sent?
0
Ejgil HedegaardCommented:
Did not observe.
I uploaded an Excel file with the program to use 2 files, but probably due to the length of the file name, the file was converted to something else without any warning.
Here is the file.
Split-data-program.xlsm
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Thank you it is OK now :)

Regards,

Dallag
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.