Update Highlighted columns, then sort importated data

Hello Experts,

This is an additional request to http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28584697.html

The original question was solved but I'd like to add 2 tweaks.

1) Instead of opening and updating all the columns, I'd like to select the columns to be updated.  Selection could be done by highlighting the columns or cells within the columns.

2) If a new account number is added to the data table in Accounts.xlsx, then sort in ascending order  on column A, (A10 to last column, last row)

(samples files attached)
Thanks,
Ron

Sub test()

Application.ScreenUpdating = False
Dim cw, nw As Workbook
Dim cs, ns As Worksheet

Set cw = ActiveWorkbook
Set cs = cw.Sheets(1)

lastrow = cs.Cells(cs.Rows.Count, "A").End(xlUp).Row 'important for sheet names check
If lastrow < 10 Then
    lastrow = 10
End If
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

    addedcounter = 0
For cols = 2 To lastcol Step 1
    'open file

    Set nw = Workbooks.Add(cs.Cells(7, cols).Value & "\" & cs.Cells(8, cols).Value)
    For Each foundsheet In nw.Sheets
        foundsheet_bool = False
        For foundrows = 10 To (lastrow + addedcounter) Step 1
            If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                'found correct sheet, now find col_y last number
                foundsheet_bool = True
                lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
            End If
        Next
        
        If Not (foundsheet_bool) Then
                
                cs.Cells(lastrow + addedcounter, 1).Value = foundsheet.Name
                For foundrows = 10 To (lastrow + addedcounter) Step 1
                    If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                        'found correct sheet, now find col_y last number
                        lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                        cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                    End If
                Next
                addedcounter = addedcounter + 1
        End If
    Next
    nw.Close
    
Next

Application.ScreenUpdating = True
End Sub

Open in new window

Accounts.xlsx
DataFile1.xlsx
bikeskiAsked:
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.

Saqib Husain, SyedEngineerCommented:
For part 1 try replacing line 17 with

For Each col In Selection.EntireColumn.Columns
cols = col.Column
0
bikeskiAuthor Commented:
Hello Syed,

That worked for part 1. Any suggestions for part 2?
0
Saqib Husain, SyedEngineerCommented:
Do you want it sorted as soon as a value is entered?
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

bikeskiAuthor Commented:
I believe at the end of the all the imports will work
0
KimputerCommented:
here's the code, thanks to previous Expert for added selected columns solution:

Sub test()

Application.ScreenUpdating = False
Dim cw, nw As Workbook
Dim cs, ns As Worksheet

Set cw = ActiveWorkbook
Set cs = cw.Sheets(1)

lastrow = cs.Cells(cs.Rows.Count, "A").End(xlUp).Row 'important for sheet names check
If lastrow < 10 Then
    lastrow = 10
End If
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

    addedcounter = 0
'For cols = 2 To lastcol Step 1
    'open file
For Each col In Selection.EntireColumn.Columns
 cols = col.Column
 
    Set nw = Workbooks.Add(cs.Cells(7, cols).Value & "\" & cs.Cells(8, cols).Value)
    For Each foundsheet In nw.Sheets
        foundsheet_bool = False
        For foundrows = 10 To (lastrow + addedcounter) Step 1
            If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                'found correct sheet, now find col_y last number
                foundsheet_bool = True
                lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                lasts = foundsheet.Cells(foundsheet.Rows.Count, "s").End(xlUp).Row
                If lasty > lasts Then
                    cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                Else
                    cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasts, 19).Value
                End If
            End If
        Next
        
        If Not (foundsheet_bool) Then
                
                cs.Cells(lastrow + addedcounter, 1).Value = foundsheet.Name
                For foundrows = 10 To (lastrow + addedcounter) Step 1
                    If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                        'found correct sheet, now find col_y last number
                        lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                        cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                    End If
                Next
                addedcounter = addedcounter + 1
        End If
    Next
    nw.Close
        
Next

    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A10:EW" & lastrow + addedcounter)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With

Application.ScreenUpdating = True
End Sub

Open in new window


Also include the ability to check column s instead of just column y (as asked in other question)
0
bikeskiAuthor Commented:
Hi Kimputer,

The code works fine when pulling data from column Y but not from column S.  I tested it on a workbook that had some worksheets with S column totals and some with Y column totals, only the Y columns came through. I tested on a workbook with only Y columns, (that worked) and only S columns, that didn't work.

One more element, the code needs to erase the column section data in the Accounts.xlsx file before it updates. i.e. if I'm updating column C, then erase data from C9:Cxx
0
KimputerCommented:
Please send the file with both s and y columns.
What needs to be the result? I had a feeling it was either s or y, not both.  How to know which one to take?
Then also send one with only column s.
Only send files that didn't work, so I can start to check how or why it didn't work.
0
bikeskiAuthor Commented:
Attached is a samples file. 3 of the worksheets have the balance total in Y and 3 in S. The balance will always be the last column of the worksheet.

The worksheets that have the Y column will work, the S columns do not. I've added columns to get the balance to Y and then deleted the columns back to S.

Thanks
TestData11.xlsx
0
KimputerCommented:
Updated (fixed S code, also as per request, delete data (A9 and on) first):

Sub test()

Application.ScreenUpdating = False
Dim cw, nw As Workbook
Dim cs, ns As Worksheet

Set cw = ActiveWorkbook
Set cs = cw.Sheets(1)

cs.Range("A9:XFD99999").Value = ""

lastrow = cs.Cells(cs.Rows.Count, "A").End(xlUp).Row 'important for sheet names check
If lastrow < 10 Then
    lastrow = 10
End If
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

    addedcounter = 0
'For cols = 2 To lastcol Step 1
    'open file
For Each col In Selection.EntireColumn.Columns
 cols = col.Column
 
    Set nw = Workbooks.Add(cs.Cells(7, cols).Value & "\" & cs.Cells(8, cols).Value)
    For Each foundsheet In nw.Sheets
        foundsheet_bool = False
        For foundrows = 10 To (lastrow + addedcounter) Step 1
            If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                'found correct sheet, now find col_y last number
                foundsheet_bool = True
                lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                lasts = foundsheet.Cells(foundsheet.Rows.Count, "s").End(xlUp).Row
                If lasty > lasts Then
                    cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                Else
                    cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasts, 19).Value
                End If
            End If
        Next
        
        If Not (foundsheet_bool) Then
                
                cs.Cells(lastrow + addedcounter, 1).Value = foundsheet.Name
                For foundrows = 10 To (lastrow + addedcounter) Step 1
                    If foundsheet.Name = CStr(cs.Cells(foundrows, 1).Value) Then
                        'found correct sheet, now find col_y last number
                        lasty = foundsheet.Cells(foundsheet.Rows.Count, "y").End(xlUp).Row
                        lasts = foundsheet.Cells(foundsheet.Rows.Count, "s").End(xlUp).Row
                        If lasty > lasts Then
                            cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasty, 25).Value
                        Else
                            cs.Cells(foundrows, cols).Value = foundsheet.Cells(lasts, 19).Value
                        End If
                    End If
                Next
                addedcounter = addedcounter + 1
        End If
    Next
    nw.Close
        
Next

    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A10:EW" & lastrow + addedcounter)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With

Application.ScreenUpdating = True
End Sub

Open in new window

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
bikeskiAuthor Commented:
So close, it all works well except instead of deleting all the data with cs.Range("A9:XFD99999").Value = "", I'd like just the selected column data deleted.

Thanks
0
bikeskiAuthor Commented:
I gave Kimputer the majority of the points as he provided the complete code and answered the difficult portion. (what I perceived as difficult:)

I posted a new question at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28601681.html to resolve the one remaining issue, thus the grade B.

Thanks Experts  for your help
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.