You need to put all the files into the same folder. Also the Sumarry
workbook should not be in the same folder. The code is expecting the
worksheet name of the data in the 10 workbooks to be sheet1. Change the
sheet name as required.

Sub consolidate()

Folder = "C:\temp\"
FName = Dir(Folder & "*.xls")
ColCount = 3
Do While FName < ""
Set bk = Workbooks.Open(Filename:=Folder & FName)

Cells(1, ColCount) = FName
Cells(2, ColCount) = bk.Sheets("Sheet1").Range("B2")

bk.Close savechanges:=False
FName = Dir()
Loop
End Sub


" wrote:

Hi,

I have got data in say ten workbooks.

I wanted to consolidate in one sheet in another workbook.

I wanted the data in cell B2 of the sheets to be displayed in the
consolidated sheet from C2 to L2(Horizontally) i.e, data in B2 of the
sheet in the first workbook to be copied and pasted special with
values in the C2 of the consolidated sheet in the other work book,
then B2 of the sheet in the second workbook to D2, then B2 of the
third workbook to E2 of the consolidated sheet and so on.


Could you please help me in this.

Thanks,
Shaiju