Solved

Update Highlighted columns, then sort importated data

Posted on 2014-12-29
11
72 Views
Last Modified: 2015-01-22
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
0
Comment
Question by:bikeski
  • 6
  • 3
  • 2
11 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 40522935
For part 1 try replacing line 17 with

For Each col In Selection.EntireColumn.Columns
cols = col.Column
0
 

Author Comment

by:bikeski
ID: 40526021
Hello Syed,

That worked for part 1. Any suggestions for part 2?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40526336
Do you want it sorted as soon as a value is entered?
0
 

Author Comment

by:bikeski
ID: 40526347
I believe at the end of the all the imports will work
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40527413
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:bikeski
ID: 40542510
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
 
LVL 35

Expert Comment

by:Kimputer
ID: 40542987
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
 

Author Comment

by:bikeski
ID: 40545796
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
 
LVL 35

Accepted Solution

by:
Kimputer earned 400 total points
ID: 40551224
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
 

Author Comment

by:bikeski
ID: 40555540
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
 

Author Closing Comment

by:bikeski
ID: 40564219
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now