Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data Horizontally
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data Horizontally
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data Horizontally
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 " 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data Horizontally
On Oct 22, 11:10*am, Joel 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 " 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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting vertical rows and displaying them horizontally. | Excel Discussion (Misc queries) | |||
Add average data horizontally into existing chart | Charts and Charting in Excel | |||
show XML data horizontally | Excel Programming | |||
want to cut data from vertical cells and paste them horizontally. | Excel Discussion (Misc queries) | |||
copying data horizontally | Excel Worksheet Functions |