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]

While stepping through the code (F8), keep the Locals window open and you can see the data being loaded to the variables; including the array. To open the Locals window:

In the VBA Editor → View Menu → Locals Window. The variables will load to the window when you start stepping through.

The array variable will keep collapsing in the Locals window each time the ReDim statement runs, but you should see if it's loading correctly. Watch the "If" statements closely to see if they are causing the array to not load. I'm guessing there is a logic error I'm not seeing.

 

[28creation]

It says:

finalRow 7 Long
i 7 Long
n 7 Long


The arrEmps are Empty.

 

[AFPathfinder]

Inside the Do Loop, finalRow is measured by the last cell in column "B" that isn't empty (start at the last row on worksheet and searches up for a value). If the last value is in row 7 or less, it doesn't add anything to the array.

Are you sure there is data in column B below row 7 in the individual workbooks? I was assuming the individual workbooks have headers in row 7.

 

[28creation]

The headers are in row 7, then the information that needs feeding starts from row 8.

The same for each workbook.

 

[AFPathfinder]

In column B? If finalRow is getting the value of 7 in the Do Loop, it's not loading anything to the array.

 

[28creation]

B7, D7, etc.

Does it matter that the cells are merged in 2s?

 

[AFPathfinder]

If B8 is merged with C8, then its fine. When merged, the value address is the top left cell. If A8 and B8 are merged and the code is looking at cell B8, B8 is considered empty.

When you are stepping through the Do Loop, does it execute the lines that add data to the array? A lot of times, I'll test loops by putting a break inside the If statements and run the macro (F5). If the macro stops at one of the breaks, I know the loop ran across a true value in the If statement.

I'm thinking that it has to be finalRow that is causing the issues. It should be looking in the column that is most likely to contain a value past row 7. In this line of code, you would change the "2" to a different number representing the column number (B = 2).

[COLOR=#333333]finalRow = Cells(Rows.Count, [B]2[/B]).End(xlUp).Row 'Find last row in column B[/COLOR]

 

[28creation]

It jumps straight from Do While oFile <> "" to Application.ScreenUpdating = True

 

[AFPathfinder]

If that is happening, that means that the oFile line didn't find any matches:

oFile = Dir("P:\Coaching\Schemes - Denah\*" & ".xlsm")

If you didn't catch it in my previous post, make sure to update the extension in that line to the extension of the individual workbooks. I keep changing it to "xlsm" as that is the extension of my test files, but forget to change it back to xlsx before I post it here.

 

[28creation]

The extensions are all xlsm....

'Change this line to the directory that contains the workbooks
oFile = Dir("P:\Coaching\Schemes - Denah\*" & ".xlsm")

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