[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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
0
Mohammed Dallag
Asked:
Mohammed Dallag
  • 9
  • 7
1 Solution
 
NorieCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now