Info fed from various workbooks to one overview

[28creation]

Hi all,

I've got one overview workbook & several individual workbooks.

I want certain information to feed through to the overview book. I know how to do this but there's a few other bits I want to do....


I want four cells worth of information fed through from workbook #1 & the overview to have the name of workbook #1 (minus the file type) in another cell next to these four cells.

Then as info is fed into the individual workbooks the overview receives the info & adds it below the ones already received, with the name of the relevant file next to it.

Is there any way of doing this either through normal Excel means or with VBA?


Hope you can help.

Thanks, Matt

 

[AFPathfinder]

Are all the workbooks located in the same directory? Do the number of individual workbooks change?

The four cells per individual workbook sounds like it might be doable with formulas, but the name of the workbook might be best solved with VBA.

Range("E4").Value = Workbooks("Workbook1").Name

 

[28creation]

Hi, thanks for this.

All the workbooks are in the same directory yeah, same folder. One workbook may be feeding from 10 other workbooks & another might have 12.

 

[AFPathfinder]

You would probably need a Do Loop or For Each to loop through all the files in that directory and take action on each one if not already listed in the overview workbook. For example:

Sub OvView_Update()
    Dim mainReport As Workbook, oFile As String
    
    Set mainReport = Application.Workbooks("Overview.xlsx")
    
    oFile = Dir("C:\TestFolder\*.xlsx")
    
    Do While oFile <> ""
        mainReport.Activate
        If oFile <> mainReport.Name Then
            Workbooks.Open Filename:=oFile
            'Do things here
            ActiveWorkbook.Close SaveChanges:=False
        End If
        oFile = Dir
    Loop
End Sub

 

[28creation]

Sorry, could you explain this in a bit more detail please? Thanks.

 

[AFPathfinder]

Sorry for not explaining. The code I provided in my previous post is a loop that looks at each file in a particular directory. It doesn't do anything more than open and then close each workbook, but needs more code to match what you are trying to accomplish once a workbook is open.

How familiar are you with VBA/macros?

 

[28creation]

I have very little experience with VBA & macros to be honest.

If each file is named "Team Member #1", "Team Member #2", etc, & the relevant tab in each workbook is "Feedback Log", I need cells B8, D8, F8 & H8, plus any future additions eg B9, D9, F9 & H9, etc, to be added to the Overview workbook in the next available space.

Hope this makes sense. Thanks.

 

[AFPathfinder]

So, if I understand correctly, each employee workbook may have more than one row starting in row 8 that has values that need transferred to the Overview workbook?

What cells in the overview workbook are those values being pasted to? Do the values in existing cells in row 8 change values? Do additional rows need to be grouped with other rows from the same employee workbook, or can they be added to the bottom of the list?

I'm thinking it would be best to loop through each workbook, load all the values and corresponding workbook names to an array and then paste the array values to the Overview workbook.

 

[28creation]

Yes that's right.

B8, D8, F8 & H8 will feed to D8, F8, H8 & J8, with B8 being fed with the name of the workbook (which is the name of the employee).

Of course if the first line of the overview is taken up with info from the first employee then the second employee's info needs to feed to the second line, & so on.

Many thanks for your help with this. Much appreciated.

 

[AFPathfinder]

I haven't testing this so there may need to be a few fixes, but this should be a good start. You can add this macro to your PERSONAL.xlsb workbook and attach to the Quick Access Toolbar, or you can put it in your Overview.xlsx (you'll need to convert to .xlsm to hold and run the macro) and assign the macro to a button.

You'll need to update the filepath to the correct directory in this line:

oFile = Dir("C:\TestFolder\*.xlsx")

After you input the directory, put "*.xlsx" before the second double quotes.

Here's the full code:

Sub OvView_Update()    Dim mainReport As Workbook
    Dim arrEmp() As Variant
    Dim oFile As String
    Dim finalRow As Long
    Dim i As Long
    Dim n As Long
    
    Application.ScreenUpdating = False 'Turn off screen updating to speed up macro


    'Change this line to the directory that contains the workbooks
    oFile = Dir("C:\TestFolder\*.xlsx")


    'Load workbook object of Overview workbook into variable
    Set mainReport = Application.Workbooks("Overview.xlsx")


    'Find the last used row in column B and load to variable
    finalRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    'Delete the old data in the Overview workbook
    Range("B8:B" & finalRow & ", D8:D" & finalRow & ", F8:F" & finalRow & ", H8:H" & finalRow).Clear


    'Start Looping through all files in the directory
    Do While oFile <> ""
        
        'If the file isn't named Overview.xlsx, then open and copy the necessary data to the array
        If oFile <> mainReport.Name And Right(oFile, 4) = "xlsx" Then
            Workbooks.Open Filename:=oFile 'Open file
            Sheets("Feedback Log").Activate 'Activate the Feedback Log sheet
            finalRow = Cells(Rows.Count, 2).End(xlUp).Row 'Find last row in column B
            ReDim Preserve arrEmp(4, UBound(arrEmp, 1) + finalRow - 7) 'Expand 2nd dimension of the array to hold the new data
            For i = 8 To finalRow 'Loop through the rows
                arrEmp(0, n) = ActiveWorkbook.Name  'Load workbook name
                arrEmp(1, n) = Range("B" & i).Value 'Load column B data in the row to array
                arrEmp(2, n) = Range("D" & i).Value 'Load column D data in the row to array
                arrEmp(3, n) = Range("F" & i).Value 'Load column F data in the row to array
                arrEmp(4, n) = Range("H" & i).Value 'Load column H data in the row to array
                n = n + 1 'Increase the 2nd dimension counter for next row
            Next i 'Loop to next row/exit if no more rows
            ActiveWorkbook.Close SaveChanges:=False 'Close the employee workbook
        End If
        oFile = Dir
    Loop
    
    Application.ScreenUpdating = True 'Turn screen updating back on to see values being added to overview workbook
    
    n = 8 'Turn counter into row marker
    For i = 0 To UBound(arrEmp, 2) 'Loop through the array and unload the data to the Overview workbook row by row
        Cells(n, 2).Value = arrEmp(0, i)
        Cells(n, 4).Value = arrEmp(1, i)
        Cells(n, 6).Value = arrEmp(2, i)
        Cells(n, 8).Value = arrEmp(3, i)
        Cells(n, 10).Value = arrEmp(4, i)
    Next i
End Sub