VB to combined Named Cells in diff Workbooks

[jdmill80]

Hi all!

I have 5-6 separate excel spreadsheets that contain the same info about different clients. I then combine the relevant pieces of data (by way of the good ole' copy & paste) into one spreadsheet as the data wasn't that large in size.

I foresee my 5-6 spreadsheets growing to dozens and the amount of info I need to combine to grow as well.

I was wondering if anyone knew of a vba script which would take the values from specifically named cells within targeted spreadsheets and consolidate them into one main spreadsheet.

Here's a simple example:

1. In Workbook "A.xlsx", Tab "Data", Cell name "RevQ1Y13", the value in the cell is $13.
2. In Workbook "B.xlsx", Tab "Data", Cell name "RevQ1Y13", the value in the cell is $7.
3. In Workbook "C.xlsx", Tab "Data", Cell name "RevQ1Y13", the value in the cell is $20.

My desire is to run a macro in a "Consolidation" workbook and end up with the following info in a column I'd call RevQ1Y13:
1. A = 13
2. B = 7
3. C = 20

Does this make sense? Can anybody help?

Thanks!!
J

 

[Trevor G]

Will all the workbooks reside in a single folder?

Try something like this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#007F00">'Open all workbooks</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> myPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>myPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change path</SPAN><br>MyFile = Dir(myPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open myPath & MyFile<br>Application.Goto Reference:="RevQ1Y13"<br>Selection.Copy<br>ActiveWorkbook.Close<br>Sheets("Sheet2").Activate<br>Range("A2").Select<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br>ActiveCell.PasteSpecial xlPasteAll<br>Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jdmill80]

This worked! Great job!

 

[jdmill80]

Can I ask a follow up?

If I add another reference for the script to search for, say RevQ2Y13, where would I insert it?

In other words, along with RevQ1Y13, I want to tell it to grab a few other names in the spreadsheets and paste them in additional columns.

Thanks!

 

[Trevor G]

Remove the lines

Application.Goto Reference:="RevQ1Y13"
Selection.Copy

and use

Range("RevQ1Y13, RevQ2Y13").Copy

 

[jdmill80]

This is definitely the direction I want.

Instead of having a RANGE, can I just list the names i want and then the columns i want them to be in?

In other words, instead of:

Range("RevQ1Y13, RevQ2Y13").Copy

and below having it paste to:

Range("E4").Select

Can I list them (something like):

"RevQ1Y13, RevQ2Y13, RevQ3Y13, ExpQ1Y13, ExpQ2Y13"

and have it paste to:

"E4, F4, G9, J29, K29"

The benefit being that I can pick and choose the cells on the spreadsheet without them being in a range or physical position.

Thanks!

 

[Trevor G]

In my opinion you have to specify range and also the destination range as well for each cell. Even if you declare them you have to specify the range.

Look at this. I have not tested this, but the idea is that as you open the first book through the loop it copies the named cell and then places it in the workbook you want it go into. I see this as a problem as you want to open multiple workbooks and you are wanting to specify a unique cell !

Range("RevQ1Y13").Copy Destination: book2.Sheets("sheet2").Range ("E4")
Range("RevQ2Y13").Copy Destination: book2.Sheets("sheet2").Range ("F4")

 

[jdmill80]

Right, that's the idea.

In a folder, go through all excel docs and find cell names "A", "B", "C", and then paste them to columns A1, B1, C1, etc.

If I could do that, I could later include additional spreadsheets by adding them to the directory and add cell names (D, E, F, G...).

What do you think?

Keep in mind, I don't mind having to edit the script to facilitate additional spreadsheets or cell names. So I don't mind having to add the file names manually or have the script grow in size.

In the end, any time spent maintaining the script will be much less than the time I'm currently spending copying and pasting to consolidate this information.