How to combine multiple spreadsheets into one?

I hope I can explain this well enough. I have Sales Orders from customers that come in Excel format. Every SO is in the exact same format. The only difference is the SO # and the line items on the SO (qty, part and price). So there are two parts to this. The first is the  SOImport spreadsheet. The 2nd part are the individual SO's, SO-1, SO-2, SO-3, etc. We are then taking the individual SO's (SO-1, SO-2, etc) and manually putting them in the spreadsheet, "SOImport". We then take the SOImport spreadsheet and import it into our software.

I am trying to figure out a way we can put these individual SO's in a folder, run a script and have the SO#, part #, qty and price from all the SO's populate on the SOImport spreadsheet. I also need a way the SO Numbers can automatically be created when pushed to to the SOImport spreadsheet.

I have attached the SOImport spreadsheet template and also an example of 2 sales orders, SO-1 and SO-2. The individual SO's aren't named SO-1, SO-2, etc. I just did that so it hopefully makes sense.

Is there a way to put all these individual SO's (SO-1, SO-2, etc) in the SOImport spreadsheet template like you will see on my example?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saqib Husain, SyedEngineerCommented:
This macro will work if the source and target files are open and the source file is active.

Sub post2SOimport()
    Dim cel As Range
    Dim tr As Long
    Dim sws As Worksheet
    Dim tws As Worksheet
    Set sws = ActiveSheet
    Set tws = Workbooks("SOImport.xlsx").Worksheets("POOrderLine")
    For Each cel In sws.Range("A10", sws.Range("A" & Rows.Count).End(xlUp))
        tr = tws.Range("B" & tws.Rows.Count).End(xlUp).Offset(1).Row
        tws.Cells(tr, 2) = sws.Range("B1")
        tws.Cells(tr, 5) = cel.Offset(, 2)
        tws.Cells(tr, 10) = cel
        tws.Cells(tr, 11) = cel.Offset(, 3)
        tws.Cells(tr, 11).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"
    Next cel
End Sub

Open in new window

Harry LeeCommented:

Use the following code. This allows you to pick which folder where the SOs are stored, and import all the SOs stored in the folder all at once.

I have also attached the test file you have uploaded, with the macro in it. The macro name is ImportSOs

Test it out and let me know if you need minor tuning.

Option Explicit
Function GetFolder(InitDir As String) As String
Dim fldr As FileDialog
Dim sItem As String
sItem = InitDir
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If Right(sItem, 1) <> "\" Then
sItem = sItem & "\"
End If
.InitialFileName = sItem
If .Show <> -1 Then
sItem = InitDir
sItem = .SelectedItems(1)
End If
End With
GetFolder = sItem
Set fldr = Nothing
End Function
Sub ImportSOs()
    Dim strPath As String, strFile As String
    Dim swbk As Workbook, twbk As Workbook
    Dim tws As Worksheet, sws As Worksheet
    Dim twsn As String
    Dim CurRw As Long, I As Long
    Set twbk = ActiveWorkbook
    twbk.Sheets.Add.Name = Format(Now, "yyyymmddhhmmss") & "SOImport"
    Set tws = ActiveSheet
    tws.Cells(1, 1) = "(blank)"
    tws.Cells(1, 2) = "Order#"
    tws.Cells(1, 3) = "(blank)"
    tws.Cells(1, 4) = "(blank)"
    tws.Cells(1, 5) = "ItemNo"
    tws.Cells(1, 6) = "(blank)"
    tws.Cells(1, 7) = "(blank)"
    tws.Cells(1, 8) = "(blank)"
    tws.Cells(1, 9) = "(blank)"
    tws.Cells(1, 10) = "OrderQty"
    tws.Cells(1, 11) = "EAPrice"
    tws.Cells(1, 13) = "Import Ref"
    tws.Rows(1).Font.Bold = True
    CurRw = 2
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.xlsx")
    Do While strFile <> ""
        If Right(strFile, 4) = "xlsx" Then
            Set swbk = Workbooks.Open(Filename:=strPath & strFile)
            Set sws = ActiveSheet
                For I = 10 To sws.Cells(Rows.Count, 1).End(xlUp).Row
                    tws.Cells(CurRw, 2) = sws.Cells(1, 2)
                    tws.Cells(CurRw, 5) = sws.Cells(I, 3)
                    tws.Cells(CurRw, 10) = sws.Cells(I, 1)
                    tws.Cells(CurRw, 11) = sws.Cells(I, 4)
                    tws.Cells(CurRw, 11).NumberFormat = "$#,##0.00_);($#,##0.00)"
                    tws.Cells(CurRw, 13) = "From " & strFile
                    CurRw = CurRw + 1
            swbk.Close SaveChanges:=False
        End If
        strFile = Dir
End Sub

Open in new window

brasimanAuthor Commented:
Thank you Harry and Syed!

Harry, your scripts works great, thanks!

The only other questions is how do I get the SO numbers to automatically generate. When the SO-1 and SO-2, etc orders are entered, those SO numbers aren't ours. I was hoping to have something that generates the numbers. Is there a way when the script runs, it asks for the starting SO number?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

brasimanAuthor Commented:
Also, another quick question. Instead of creating a new tab at the bottom, is it possible to have the data fill on the current tab that is open? On Sheet 1, I have all these headers already there, is it possible to have the data just populated on Sheet 1 starting on row 2? That way it just uses the headers I already have.
Harry LeeCommented:

Answer 1 - On your first question, I don't think the SO number generation is supposed to be in this workbook. Since the SO is created somewhere else, or some other workbook you have, the auto generation of SO number has to be included in the workbook or system that you create them with.

Answer 2 - I did it on purpose to create new tab each time you import a batch of SOs, so that nothing is overwritten or deleted. The sheet is also named using the date and time as an identifier. It's kind of a backup system. If you like it to just fill your list, I can adjust the macro to do it.

Question 1 - Do you want the list to be overwritten every time you import a batch of SOs? or do you want me to keep adding it to the bottom?
brasimanAuthor Commented:
#1 - The SO-1, SO-2, etc are just orders with no SO numbers assigned to them yet. When the macro you created is ran, i was hoping it would take the SO-1, SO-2, etc and create SO numbers since they don't have SO numbers yet. That way I don't have to go down line by line and enter SO numbers.

#2 - I definitely understand why you made it a separate tab! In my case, I will just create a button on Sheet 1 to run the macro, and if it can populate the rows of data starting on Row 2, that would help me tremendously. I will then do Save As so it preserves the "template" with the macro. Then I can take the new file and use that to import the SO's.
Harry LeeCommented:

In that case, the next PO number has to be stored somewhere on the sheet, and that number is automatically increased by one each time a new PO is imported. I have put that in cell O1.

I have modified the macro to do it the way you like it. Check it out.
brasimanAuthor Commented:
Hi Harry. Thank you!! The last question I have is the SO number. That works great to have the SO number in cell O1. However, is there a way for me to put the beginning SO number in O1, then run the macro. Then for every SO, it just increases the number by 1 on the SOImport.xlsm?
Harry LeeCommented:
It is doing that now. If you import multiple SOs, it will keep increasing.

You don't delete that off O1. It stays there forever. You don't reset it at all. It will keep growing.
brasimanAuthor Commented:
Thanks Harry. I guess my questions is can I have that SO# field (O1) generate the Order Number starting on B2? So in O1, I put 100. If i have 5 SO's, starting in B1, the first SO is 100, the 2nd SO is 101, etc. Is that something that can be done?
Harry LeeCommented:
Oh, sorry for the misunderstanding.

Of course you can.

Please test this.

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
brasimanAuthor Commented:
Harry you da man! Thank you. Thats perfect. I appreciate your help!!!
brasimanAuthor Commented:
Harry did awesome!! Appreciate his help!
Harry LeeCommented:
You are welcome. I'm glad I can help.

brasimanAuthor Commented:
I appreciate it!
brasimanAuthor Commented:
I need to make an adjustment to the code. I had to make a change to the template. I will be posting another question here in a few minutes. Hopefully you can help me with this adjustment! :)
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

From novice to tech pro — start learning today.