I have multiple workbooks that I retrieve data from, the workbook count can
change month to month. I bring this data into a separate workbook to produce
the report.
Lets say the multiple workbooks are 1,2,3,4 and my report is "Summary". In
all workbooks column A contains the store # (which could be interpreted as
the primary key)

In column B of Summary it will look for the matching store #'s in column B
of workbook 1 and retrieve the data in column C then place that data in
column C of the Summary. Once the all the data has been retrieved I want it
to move to column C of the summary and start looking for data from workbook 2
repeating the process etc...

The problem I have is, once I have all the data from workbook 1, how do I
move to workbook 2. Getting the data out and into the summary is not the
issue, just going to the next workbook

Thanks for the help!

Maybe some approach like this:

Dim bksum as workbook, rngSum as Range
Dim bk1 as workbook, rng1 as Range
Dim bk2 as Workbook, rng2 as Range
Dim cell as Range, rng as Range
Dim res as Variant
set bkSum = Workbooks("Summary.xls")
set bk1 = Workbooks("Book1.xls")
set bk2 = Workbooks("book2.xls")

with bkSum.worksheets(1)
set rngSum = .range(.Cells(2,2),.Cells(2,2).End(xldown))
End with

with bk1.Worksheets(1)
set rng1 = .Range(.Cells(2,2),.Cells(2,2).End(xldown))
End with

with bk2.Worksheets(1)
set rng2 = .Range(.Cells(2,2),.Cells(2,2).end(xldown))
End with

for each cell in rngSum
res = Application.Match(cell,rng1,0)
if not iserror(res) then
set rng = rng1(res)
cell.offset(0,1).Value = rng.offset(0,1)
end if
Next

for each cell in rng.Sum.Offset(0,1)
if cell < "" then
res = Application.Match(cell,rng2,0)
if not iserror(res) then
set rng = rng2(res)
cell.offset(0,1).Value = rng.offset(0,1).Value
end if
end if
Next

--
Regards,
Tom Ogilvy

"Hans" wrote:

I have multiple workbooks that I retrieve data from, the workbook count can
change month to month. I bring this data into a separate workbook to produce
the report.
Lets say the multiple workbooks are 1,2,3,4 and my report is "Summary". In
all workbooks column A contains the store # (which could be interpreted as
the primary key)

In column B of Summary it will look for the matching store #'s in column B
of workbook 1 and retrieve the data in column C then place that data in
column C of the Summary. Once the all the data has been retrieved I want it
to move to column C of the summary and start looking for data from workbook 2
repeating the process etc...

The problem I have is, once I have all the data from workbook 1, how do I
move to workbook 2. Getting the data out and into the summary is not the
issue, just going to the next workbook

Thanks for the help!

Tom,

Thanks for the response, I will play with it and let you know what I come up
with.

Hans

"Tom Ogilvy" wrote:

Maybe some approach like this:

Dim bksum as workbook, rngSum as Range
Dim bk1 as workbook, rng1 as Range
Dim bk2 as Workbook, rng2 as Range
Dim cell as Range, rng as Range
Dim res as Variant
set bkSum = Workbooks("Summary.xls")
set bk1 = Workbooks("Book1.xls")
set bk2 = Workbooks("book2.xls")

with bkSum.worksheets(1)
set rngSum = .range(.Cells(2,2),.Cells(2,2).End(xldown))
End with

with bk1.Worksheets(1)
set rng1 = .Range(.Cells(2,2),.Cells(2,2).End(xldown))
End with

with bk2.Worksheets(1)
set rng2 = .Range(.Cells(2,2),.Cells(2,2).end(xldown))
End with

for each cell in rngSum
res = Application.Match(cell,rng1,0)
if not iserror(res) then
set rng = rng1(res)
cell.offset(0,1).Value = rng.offset(0,1)
end if
Next

for each cell in rng.Sum.Offset(0,1)
if cell < "" then
res = Application.Match(cell,rng2,0)
if not iserror(res) then
set rng = rng2(res)
cell.offset(0,1).Value = rng.offset(0,1).Value
end if
end if
Next

--
Regards,
Tom Ogilvy

"Hans" wrote:

I have multiple workbooks that I retrieve data from, the workbook count can
change month to month. I bring this data into a separate workbook to produce
the report.
Lets say the multiple workbooks are 1,2,3,4 and my report is "Summary". In
all workbooks column A contains the store # (which could be interpreted as
the primary key)

In column B of Summary it will look for the matching store #'s in column B
of workbook 1 and retrieve the data in column C then place that data in
column C of the Summary. Once the all the data has been retrieved I want it
to move to column C of the summary and start looking for data from workbook 2
repeating the process etc...

The problem I have is, once I have all the data from workbook 1, how do I
move to workbook 2. Getting the data out and into the summary is not the
issue, just going to the next workbook

Thanks for the help!