Open Excel File, read worksheets, select last number in column

Hello Experts,
I need help to write the macro. I've started with a very basic script, but need help to fill in the details. Let me know if this is too much, I can break the steps into several requests. (sample files attached)

Details:
From the Accounts file, I need to open DataFile1.xlsx and select each worksheet, read the final number in column Y, then copy that corresponding number back to the Accounts file. If the worksheet name in the DataFile1 is not listed in Accounts, then a row needs to be inserted in Accounts with that worksheet name.

Outline:


Sub OpenFile()
  'from the highlighted column, select the pathname and filename in rows 7 & 8, open the file
  Workbooks.Open Filename:=Pathname&Filename
 'read the worksheet names,
 'If not found in Accounts, then insert row() with worksheet name. EndIf
 'Find last number in column Y, copy to Accounts of corresponding row
End Sub

Thanks,

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

KimputerCommented:
This code should have almost everything you specified (can't figure out what you mean with 'If not found in Accounts, then insert row() with worksheet name. EndIf' yet.
For now my code searches, if found write it down. If not found, then add to a string at the last row (so in Accounts, cell A12 should say something like "list of sheet names not found" or something):

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
lastcol = cs.Cells(7, cs.Columns.Count).End(xlToLeft).Column 'to check how many files to open and read

For cols = 2 To lastcol Step 1
    'open file
    stringcounter = 0
    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 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
            
                If (InStr(cs.Cells(lastrow + 1, cols).Value, foundsheet.Name) <= 0) Then
                    If cs.Cells(lastrow + 1, cols).Value = "" Then
                        cs.Cells(lastrow + 1, cols).NumberFormat = "@"
                        cs.Cells(lastrow + 1, cols).Value = foundsheet.Name
                        
                    Else
                        If stringcounter = 1 Then
                            cs.Cells(lastrow + 1, cols).Value = CStr(cs.Cells(lastrow + 1, cols).Value) + "," + foundsheet.Name
            
                        Else
                            cs.Cells(lastrow + 1, cols).Value = cs.Cells(lastrow + 1, cols).Value + "," + foundsheet.Name

                        End If
                        
                    End If
                End If
                stringcounter = stringcounter + 1
        End If
    Next
    nw.Close
    
Next

Application.ScreenUpdating = True
End Sub

Open in new window


Edit: Oh wait, I think I know what you mean, but I need more time for that. So you want to go through sheet you will ever encounter, right?
It means all sheets will have a column Y with the last line you want? Because it was quite misleading then (cell A10 and A11 in Accounts). You should have started out as saying cell A10 till Axx will be used for all sheets encountered.
Update tomorrow I hope, because that made it quite a bit more complicated.
0
bikeskiAuthor Commented:
Hi Kimputer,
Sorry for the delay in my reply, the holidays have been quite busy. I haven't had a chance to try the macro yet but wanted to get your a response.

Yes, I would like the macro to add the account number/worksheet name in DataFile1.xlsx not yet listed in Accounts.xlsx cells A10 thru Axx. I hope that makes sense.

Thx
0
bikeskiAuthor Commented:
Kimputer,, your code works. I see how you're listing the accounts not found at the bottom.  Are you able to finish the insert row for non listed accounts? I can always start another thread. Thx
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

KimputerCommented:
Actually, the change from current code to the one you need (automatically insert the list) will take a lot of extra code. I will need a bit more time to think about it and then code it.
0
KimputerCommented:
Updated the code. Your first example had A10 and A11 prefilled with information. This is NOT necessary anymore (of course, you can, but it doesn't seems logical):

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

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:
Thanks for completing the project, it works well.
0
bikeskiAuthor Commented:
Hello Kimputer,

FYI, I've posted a new question with some tweaks for this solution. After I worked with the file a bit, I realized it would be better to select the files/columns in Accounts.xlsx instead of re-importing every time. Also, I'd like to sort the data to keep the account numbers organized.

The new questions is at:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28588137.html
0
bikeskiAuthor Commented:
Hi Kimputer,

I'm finding that some of my Data files are generating the balance total in column S instead of column Y.  I've tried changing the code to read:

lasty = foundsheet.Cells(foundsheet.Rows.Count, "s").End(xlUp).Row

but that's not working. Any suggestions? It might be best to test which column has the data and then calculate from there.

Thx
0
KimputerCommented:
I think I will integrate this in that follow up question. But again, I think I need more time.
0
bikeskiAuthor Commented:
Sounds good, Thx.
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.