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.

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.