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.

When I've F8 through the macro it comes up with this....

Workbook Name.xlsm is not being imported.
Worksheet Name: Feedback Log
Last Row in Column B: 7

 

[AFPathfinder]

Take a look at that workbook and see if it is correct or if there is something wrong with cell B8. You can try using a few commands in the Immediate Window to see what might be happening.

? Range("B8").Value

Select cell B8 and run this one to see which cell is active:

? ActiveCell.Address

 

[28creation]

Sorry, I don't understand what you mean with running that script.

There's nothing wrong with cell B8, it holds the Broker Ref & is merged with cell C8.

 

[AFPathfinder]

Well, the MsgBox only pops up when this is false:

If finalRow > 7 Then

The number "2" in this line designates the column that it looks in to find the last value. It starts with the last row on the worksheet and uses the keyboard shortcut to go up "End(xlUp)" to the last cell with a value.

finalRow = Cells(Rows.Count, 2).End(xlUp).Row

This leads me to believe that the value showing in cell B8 must have something wrong with it. However, the example you shared with me works 100% on my end.

Running some of the Immediate Window code would be my next step in troubleshooting the issue when you have that problem. When you select a merged cell and run this line in the Immediate Window, it will return the value of the lead cell within the merge (the cell reference that contains the value when a value is in the merged cells). Just make sure the workbook and worksheet in question is active on screen before running it.

? ActiveCell.Address

(Don't forget the question mark and space in the front)

This line in the Immediate Window will return what value is actually in cell B8 of the active workbook/active worksheet.

? Range("B8").Value

 

[28creation]

Where do these two bits of code need to be placed?

 

[AFPathfinder]

In the "Immediate" window. It's a separate window like the Properties or Project windows. It allows the user to Run a single line of code which is useful in troubleshooting or running single bits of code. For example, if a macro turns off screen updating and runs into an error before turning it back on again, you can run the "Application.ScreenUpdating = True" line in the immediate window to turn it back on. I also use it to quickly find the "ColorIndex" value of colored cells in a worksheet or convert an A1 style formula into an R1C1 formula.

If the window isn't already open, press Ctrl + G (you can also select it from the "View" menu drop down). Just enter the line of code and press Enter at the end of the line. If the code line starts with "?" (and there are no errors), the return value will show on the next line.

 

[28creation]

? Range("B8").Value returns the information I want it to

? ActiveCell.Address returns $B$8

 

[AFPathfinder]

Shot in the dark...Try removing the parameters "UpdateLinks:=False" and "ReadOnly:=True" from the "Workbooks.Open" and see if that changes how the macro runs:

Workbooks.Open Filename:=oFile 'Open file

The two Immediate code lines returned exactly what it should have. The only other thing I can think of is to add the workbook designation when it activates the Feedback Log worksheet (maybe it's not looking at the right workbook when it searches last row).

 

[28creation]

No change. Still results in the workbook not imported error.

 

[AFPathfinder]

Try changing this:

Sheets("Feedback Log").Activate 'Activate the Feedback Log sheet

To this:

Workbooks(oFile).Sheets("Feedback Log").Activate 'Activate the Feedback Log sheet