You are here:

# Excel/Pulling cell values into a consolidated workbook

Question
I've been reading through your questions and it seems like this should be relatively simple.  I have 5 workbooks where employees input certain activitites, there are at least 3 columns for each employee and each wb has 8-15 employees. Additionally, there are 10 sheets in each workbook, all set up the same as the page before it.

wb1, worksheet 1
cell 2a=cindy, cell 2b=3 shows Cindy did 3 accounts on monday
cell 3a=doug, cell 3b=4 shows Doug doing 4 accounts on monday
cell 4a=etc, etc, etc, until the bottom cell, 15b, which adds all the accounts for the day
worksheet 2 is Tuesday

wb2 the same with another team of employees
wb3 etc, wb4 etc, wb5 etc

consolidated workbook is in the same folder, and needs to take wb1, cell 15b and insert it into 2b (with 2a being the name of the team) then wb2, 15b to line 3b, etc.

I hope I explained this adequately.  When I was thinking it, it seemed so simple, then putting it in words made it complicated...

Can you help?

Ginger

Assume the team name is the workbook name.  You say you have 5 workbooks (5 teams) and each workbook has 10 sheets each sheet representing a days work for the team of the workbook.  In your consolidation example, you take only one cell value from each wb - Also, you list the single cell in a different row in column B but you list them down the single column under the team name of the first workbook.  This doesn't make sense to me.  I will assume you want the workbook name/team name in row1.  Then starting in column B you want to list 10 values, one from each day/sheet.  Each workbook will be in a separate column, so wb1 in B, wb2 in C up to column F

wb1  wb2  wb3  wb4  wb5
5        6
3        2
7        3

down to 11 for 10 days

Sub consolidateworkbooks()
Dim v As Variant, bk1 As Workbook, sh1 As Worksheet
Dim bk As Workbook, sh As Worksheet, icol As Long
Dim i As Long, rw As Long

v = Array("wb1.xls", "wb2.xls", "wb3.xls", "wb4.xls", "wb5.xls")
Set bk1 = Workbooks("Master")
Set sh1 = bk1.Worksheets(1)
icol = 1
For i = LBound(v) To UBound(v)
Workbooks.Open bk1.Path & "\" & v(i)
Set bk = ActiveWorkbook
icol = icol + 1
sh1.Cells(1, icol) = bk.Name
rw = 2
For Each sh In bk.Worksheets
sh1.Cells(rw, icol).Value = sh.Cells(15, "B").Value
rw = rw + 1
Next
bk.Close SaveChanges:=False
Next
End Sub

--
Regards,
Tom Ogilvy

Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 9 Politeness = 10 Comment Tom came back with his answers so fast, I am really impressed and appreciate that very much!

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.