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
down to 11 for 10 days
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