How to combine multiple spreadsheets into one, part 2

Harry Lee helped me with this awesome code but i need to make a little tweak.  On the attached spreadsheet, the Part column C was populating on another spreadsheet. It works PERFECT! But, I had to change where we put the part number. It is now in column G, the Comments column. Long story why. :) I tried changing the code to look to column G, but then the "Order Total" showed up on the SOImport spreadsheet. Before i made that change, that didn't happen. So obviously there is something that says take all the parts, put them on the SOImport spreadsheet, but don't include the "Order Total" line. I just don't know where that is. Could I get some help with that? I attached the SO showing the change.
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.

Harry LeeCommented:

I have modified the code.

This is how you modify the code.

The line
tws.Cells(CurRw, 5) = sws.Cells(I, 3)

Open in new window

is what copy the Part Number.
What it means is The value of column E of current row (Cells(CurRw, 5)) on Target Worksheet (tws) is the value of the cell value of column C of the processing row (Cells(I, 3)) on the Source Worksheet (sws).

In order the change the reference of the source worksheet, you work on the sws.Cells(I, 3). the 3 mean the 3rd column, which is Column C. To change it to Column G, which is the 7th column, you change the 3 to 7.

Please test it out.
brasimanAuthor Commented:
Ok, that works for the most part. The issue now is the row on the SO that says Order Total carries over to the SOImport.xlsm. Why is it including that row?
brasimanAuthor Commented:
I attached the results when the macro is run. I highlighted the rows that are now showing up that shouldn't
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Harry LeeCommented:

The code use column A to locate the detail rows on your sales orders.

If there is anything in Column A or the Order Total line, the line will be picked up.
brasimanAuthor Commented:
It didn't do that before though. The only difference is the SO now has the part in column G.
Harry LeeCommented:
Although I'm pretty sure I use Column A to identify the detail line to pickup, I don't mind a second look.

Can you please upload the SO file that the total like is picked up? You can change the data for test purpose but don't just delete data off the file. I need to know where data can appear on the SO files.
brasimanAuthor Commented:
I uploaded it on 2015-04-30 at 13:20:34. I called the upload SOImport-Results.xlsm.
brasimanAuthor Commented:
BTW, I have an adjustment to the spreadsheet but asked a different question. I called the question: How to combine multiple spreadsheets into one, part 3. Thanks!
Harry LeeCommented:

I found the reason why the Total like is picked up.

Look at the sample file. There is a "Space" in A17, which is the reason why it got picked up.

When a cell contains "Space", it's not considered empty.

What software do you generate the SO from? Can you check why is there a space?

Harry LeeCommented:

I have added the check for the space on the Total line.

This file has the check for the space on the total line, and avoid the Total line being picked up.

The code for the sub
Sub ImportSOs()
'   Declare Variables
    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, SOLstRw As Long

'   Set twbk Object Variables Values
    Set twbk = ActiveWorkbook

'   Create new sheet naming using date and time

'    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
'   Use existing sheet POOrderLine as the target sheet
    Set tws = twbk.Sheets("POOrderLine")
'   Removing old data from POOrderLine sheet
    If Len(Cells(2, 2)) <> 0 Then
        tws.Range(Rows(2), Rows(Cells(Rows.Count, 2).End(xlUp).Row)).EntireRow.Delete
    End If
'   Get SOs folder from user
    strPath = GetFolder("C:\") & "\"
    strFile = Dir(strPath & "*.xlsx")
'   Define current working row
    CurRw = 2
'   Loop through all files in user specified folder tor workbooks
    Do While strFile <> ""
        If Right(strFile, 4) = "xlsx" Then
            Set swbk = Workbooks.Open(Filename:=strPath & strFile)
            Set sws = ActiveSheet

'   Find the Last Valid Row of SO Detail
    SOLstRw = sws.Cells(Rows.Count, 1).End(xlUp).Row
    If Len(Application.WorksheetFunction.Substitute(sws.Cells(SOLstRw, 1), " ", "")) = 0 Then
    SOLstRw = SOLstRw - 1
    End If
'   Loop through all detail lines of SO files and copy onto POOrderLine sheet
                For I = 10 To SOLstRw
                    tws.Cells(CurRw, 2) = tws.Cells(1, 15)
'                    tws.Cells(CurRw, 5) = sws.Cells(I, 3) 'Old location of Part Number
                    tws.Cells(CurRw, 5) = sws.Cells(I, 7) 'New location of Part Number
                    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
'   Close SO file
            swbk.Close SaveChanges:=False

'   Increase Next Order number stored in cell O1 by 1
            tws.Cells(1, 15) = tws.Cells(1, 15) + 1
        End If
'   Resume Loop
        strFile = Dir

'   Remove Object Varibles from memory
Set twbk = Nothing
Set tws = Nothing
End Sub

Open in new window


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:
Thank you!!
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.

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.