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

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


"(E-Mail Removed)" 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
>

I forgot to increment the ColCount

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")

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


"(E-Mail Removed)" 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
>

On Oct 22, 11:10*am, Joel <J...@discussions.microsoft.com> wrote:
> I forgot to increment the ColCount
>
> 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")
>
> * *ColCount = ColCount + 1
> * *bk.Close savechanges:=False
> * *FName = Dir()
> Loop
> End Sub
>
>
>
> "Shaijuzacha...@gmail.com" 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- Hide quoted text -
>
> - Show quoted text -

Thank you very much Joel.....