Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate workbook sheets to another workbook sheet
Ron DeBruin has probaly answered this question a thousand times. But I still
can't get it to work. I am trying to: Consolidate approx 100 wbs to one sheet. The primary data I want is in range G1:G80 on sheet "tracker" of each wb Secondary data exists in cell H1 of each "tracker" sheet Each wb is uniquely named I would like to add the source file name to the consolidated sheet I would like to add in row 81 of each column, the data from cell H1 in source wbs It would also be great if i could import these columns into an existing summary wb onto an existing wks. It appears Ron Debruins examples are all to copy rows. I want to do the same thing with columns. The only difference would be to add H1 to A81. I have not had muck luck as of yet figuring this stuff out for myself. To those who contribute here, please now I am only asking because I have given up on. I am screaming uncle. In Ron's code (example 4), do you have to modify the functions at all to do this? His code creates a lastrow function and it seems obvious to create a corresponding lastcol functions. Also, it seems I would have to "transpose" the destination reference and put the variable (+1) in the column portion. Thanks in advance, I am using Excel 2003, Windows XP, Caffeine, Cold Pizza, and a mental health hotline. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate workbook sheets to another workbook sheet
You have read enough, can I suggest you consider:
list all workbook names in A1 to A100 then destination workbook is ThisWorkBook source workbook is workbook(range("A" & i)) put the consolidated data starting from colume 6 (your choice) for i= 1 to 100 workbook.open source workbook range("G1:G80").copy destination workbook.cells(1, 5+i) range("H1").copy destination workbook.cells(81, 5+i) source workbook.close next i then clean up unwanted data "rudawg" wrote: Ron DeBruin has probaly answered this question a thousand times. But I still can't get it to work. I am trying to: Consolidate approx 100 wbs to one sheet. The primary data I want is in range G1:G80 on sheet "tracker" of each wb Secondary data exists in cell H1 of each "tracker" sheet Each wb is uniquely named I would like to add the source file name to the consolidated sheet I would like to add in row 81 of each column, the data from cell H1 in source wbs It would also be great if i could import these columns into an existing summary wb onto an existing wks. It appears Ron Debruins examples are all to copy rows. I want to do the same thing with columns. The only difference would be to add H1 to A81. I have not had muck luck as of yet figuring this stuff out for myself. To those who contribute here, please now I am only asking because I have given up on. I am screaming uncle. In Ron's code (example 4), do you have to modify the functions at all to do this? His code creates a lastrow function and it seems obvious to create a corresponding lastcol functions. Also, it seems I would have to "transpose" the destination reference and put the variable (+1) in the column portion. Thanks in advance, I am using Excel 2003, Windows XP, Caffeine, Cold Pizza, and a mental health hotline. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine sheets in a workbook into one sheet? | Excel Discussion (Misc queries) | |||
Need macro to consolidate all sheets in workbook | Excel Discussion (Misc queries) | |||
consolidate numbers by position from sheets in different workbook. | Excel Discussion (Misc queries) | |||
Updating sheets in Workbook from main sheet | Excel Discussion (Misc queries) | |||
Looking to save one sheet in a workbook of two sheets... | Excel Programming |