You are here:

Excel/Pulling cell values into a consolidated workbook


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?


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
bk.Close SaveChanges:=False
End Sub

Tom Ogilvy

About Excel
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


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


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.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.