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

 

[28creation]

Thanks. I'll give this a try when I get the chance, probably tomorrow.

 

[28creation]

Hi, I've made a couple of changes.

When I run the Macro a box pops up...

"Compile error:

Expected: end of statement"

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("P:\Coaching\Schemes - Denah*.xlsx")


    'Load workbook object of Overview workbook into variable
    Set mainReport = Application.Workbooks("Team 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

Hope you can help. Thanks.

 

[AFPathfinder]

Depending on the changes, everything looks to be in order. The "Expected: end of statement" compile error occurs when there isn't an ending remark to a loop, branch statement, or routine. Is "Schemes - Denah" a folder or a common beginning of workbook names?

 

[28creation]

"Schemes - Denah" is the name of the folder that's being searched.

Have I put the *.xlsx* in the right place?

 

[AFPathfinder]

You'll want another forward slash before the asterisk.

[COLOR=#333333]oFile = Dir("P:\Coaching\Schemes - Denah\*.xlsx")[/COLOR]

This may be a copy/paste error, but make sure "Dim mainReport As Workbook" is on its own line as well. I was able to run that macro without any compile errors.

 

[28creation]

Thanks for this.

I'm now getting this error...


Run-time error '9':

Subscript out of range


Any idea what's wrong?

 

[AFPathfinder]

When you hit Debug on the error screen, which line is highlighted?

 

[28creation]

Set mainReport = Application.Workbooks("Team Overview.xlsx")

I've amended this to .xlsm.

It's now coming up with this error.....

Run-time error '1004':

Cannot change part of a merged cell

When I hit debug it comes up with this line: Range("B8:B" & finalRow & ", D8:D" & finalRow & ", F8:F" & finalRow & ", H8:H" & finalRow).Clear


Basically the information coming from, for example, B8 is actually a merged cell of B8 & C8. Is this a problem?

 

[AFPathfinder]

I guess I haven't tried to clear merged cells before. You could replace that line with:

Range("[COLOR=#333333]B8:H" & finalRow).Value = ""[/COLOR]

The .Clear will remove the cell merging.

 

[28creation]

Thanks.

This is what's highlighted from the error now....

For i = 0 To UBound(arrEmp, 2) 'Loop through the array and unload the data to the Overview workbook row by row


Sorry about this. Thanks for all your help.