Copy a cell value from multiple closed workbooks into a new workbook

[sebcaris]

Hi everyone,

I am a complete novice in VBA, but I am very interested in learning as much as possible. Basically what I have is a bunch of excel files, each of them containing a value in cell J48. What I need is, to copy these values from cell J48 from the closed workbooks and put it into a new workbook. The number of closed workbooks is huge, here I just entered 3, do you guys have any suggestions?

Sub consolidateData()
Range("A1").Select
ActiveCell.Value = "Unsold Position"
Range("A2").Select
ActiveCell.Value = "Tangible Net Worth"

Range("A2").Select
Workbooks.Open Filename:="P:\1 Financial Accounting\Clotures 2016\Weekly Position Report 2016\Intermediary Valuation of unsold Position WK 2 2016.xlsx"
Workbooks.Open Filename:="P:\1 Financial Accounting\Clotures 2016\Weekly Position Report 2016\Intermediary Valuation of unsold Position WK 3 2016.xlsx"
Workbooks.Open Filename:="P:\1 Financial Accounting\Clotures 2016\Weekly Position Report 2016\Intermediary Valuation of unsold Position WK 4 2016.xlsx"

Windows("consolidate").Activate
Range("A1:A55").Select
ActiveCell.Value

Selection.Consolidate Sources:=Array( _
"'P:\Weekly Position Report 2016\[Intermediary Valuation of unsold Position WK 2 2016.xlsx]Sheet1'!J48", _
"'P:\Weekly Position Report 2016\[Intermediary Valuation of unsold Position WK 3 2016.xlsx]Sheet1'!J48", _
"'P:\Weekly Position Report 2016\[Intermediary Valuation of unsold Position WK 4 2016.xlsx]Sheet1'!J48")
Windows("Intermediary Valuation of unsold Position WK 2 2016.xlsx").Activate
ActiveWorkbook.Close

Windows("Intermediary Valuation of unsold Position WK 3 2016.xlsx").Activate
ActiveWorkbook.Close

Windows("Intermediary Valuation of unsold Position WK 4 2016.xlsx").Activate
ActiveWorkbook.Close

End Sub

[DannyJ]

Is it only the week number that is different in each of these workbooks? The rest of the file path is the same? If so, you can do it with a loop.

[BellyGas]

Welcome. We like novices who want to learn.

Firstly, when posting segments of code put them inside [ code ] [ /code ] tags (without the spaces)

Please Login or Register  to view this content.

[BellyGas]

Please Login or Register  to view this content.

No need to select and then write to, you can do this with

Please Login or Register  to view this content.

[BellyGas]

For looping through the source workbooks try:

Please Login or Register  to view this content.

You also might want to think about copying from one source workbook at a time or you could find Excel creaking with 100s of open workbooks. If your filenames aren't just increasing week numbers then you'll have to come up with some fancy maths which basically say if counter > 52 then year = year + 1, counter = 1.

[sebcaris]

This is exactly what I was looking for! thank you so much!

[sebcaris]

Thank you very much, just one more question. How can I copy the J48 values from all workbooks next to "Unsold Position" in my open workbook?

[BellyGas]

Depends how 'unsold position' is configured.

If it's a column and each value is to be copied into the next row then you can choose the next row with no data in it with:

Please Login or Register  to view this content.

Where A1048000 is the bottom of the column in question. So you could just do something like

Please Login or Register  to view this content.

[sebcaris]

[file number ffkdjsflksdj][/CODE]