Hi experts,

I have two workbooks. "All_data.xls" have 11 columns (A:K) where on column
B, I have names and on column H dates. This workbook keeps growing as we add
up new occasions.
On the other hand the other workbook "Reports.xls" have sheet names all with
the same names used (or to be used) on column B of "All_data.xls". Now I
need to be able to read data (any time) from "All_data.xls", check the names
(cell B value) and dates (cell H value) for the same line, and if both the
name and date are not written for that individual sheet (sheet with the same
name) "Reports.xls", write all the line info from "All_data.xls" to the
first available empty line of "Report.xls". Since there is no chance of
duplicates for names & dates, this way only non-repeated entried will be
written to "Report.xls".

Is it possible using macros?

Thanks in advance
Martyn

Is it possible using macros?

Yes, it should be possible.

--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Hi experts,

I have two workbooks. "All_data.xls" have 11 columns (A:K) where on column
B, I have names and on column H dates. This workbook keeps growing as we
add
up new occasions.
On the other hand the other workbook "Reports.xls" have sheet names all
with
the same names used (or to be used) on column B of "All_data.xls". Now I
need to be able to read data (any time) from "All_data.xls", check the
names
(cell B value) and dates (cell H value) for the same line, and if both the
name and date are not written for that individual sheet (sheet with the
same
name) "Reports.xls", write all the line info from "All_data.xls" to the
first available empty line of "Report.xls". Since there is no chance of
duplicates for names & dates, this way only non-repeated entried will be
written to "Report.xls".

Is it possible using macros?

Thanks in advance
Martyn

Thanks for your reply Tom,
I should have clarified that I am also looking for a solution suggestion..:)
And can you/or other interested experts please suggest a VBA code that can
do the trick?
TIA
Martyn


"Tom Ogilvy" wrote in message
...
Is it possible using macros?

Yes, it should be possible.

--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Hi experts,

I have two workbooks. "All_data.xls" have 11 columns (A:K) where on
column
B, I have names and on column H dates. This workbook keeps growing as we
add
up new occasions.
On the other hand the other workbook "Reports.xls" have sheet names all
with
the same names used (or to be used) on column B of "All_data.xls". Now I
need to be able to read data (any time) from "All_data.xls", check the
names
(cell B value) and dates (cell H value) for the same line, and if both
the
name and date are not written for that individual sheet (sheet with the
same
name) "Reports.xls", write all the line info from "All_data.xls" to the
first available empty line of "Report.xls". Since there is no chance of
duplicates for names & dates, this way only non-repeated entried will be
written to "Report.xls".

Is it possible using macros?

Thanks in advance
Martyn

it should go something like this.

Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy
Destination:=rng1.offset(rng1.rows.count,0).Resize (1,1)
end if
Next



--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Thanks for your reply Tom,
I should have clarified that I am also looking for a solution
suggestion..:)
And can you/or other interested experts please suggest a VBA code that can
do the trick?
TIA
Martyn


"Tom Ogilvy" wrote in message
...
Is it possible using macros?

Yes, it should be possible.

--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Hi experts,

I have two workbooks. "All_data.xls" have 11 columns (A:K) where on
column
B, I have names and on column H dates. This workbook keeps growing as
we
add
up new occasions.
On the other hand the other workbook "Reports.xls" have sheet names
all
with
the same names used (or to be used) on column B of "All_data.xls". Now
I
need to be able to read data (any time) from "All_data.xls", check the
names
(cell B value) and dates (cell H value) for the same line, and if both
the
name and date are not written for that individual sheet (sheet with
the
same
name) "Reports.xls", write all the line info from "All_data.xls" to
the
first available empty line of "Report.xls". Since there is no chance
of
duplicates for names & dates, this way only non-repeated entried will
be
written to "Report.xls".

Is it possible using macros?

Thanks in advance
Martyn

Hi Tom,
I used the code in the "Reports.xls" file with the All_data.xls file open
but
Received an Compile error "Invalid Outside Procedure" for the line
Set bk1 = Workbooks("All_data.xls")
Should the code reside on a seperate file?


"Tom Ogilvy" wrote in message
...
it should go something like this.

Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy
Destination:=rng1.offset(rng1.rows.count,0).Resize (1,1)
end if
Next



--
Regards,
Tom Ogilvy

No, you need to put it in a procedu

Sub Tester1()
Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy _
Destination:=rng1.offset( _
rng1.rows.count,0).Resize(1,1)
end if
Next
End Sub

I didn't put it in a procedure because I don't want to create the impression
I bench tested it - I did not, so it may contain typos but represents an
approach. It is assumed you can fine tune it to meet your needs.

--
Regards,
Tom Ogilvy



"Martyn" wrote in message
...
Hi Tom,
I used the code in the "Reports.xls" file with the All_data.xls file open
but
Received an Compile error "Invalid Outside Procedure" for the line
Set bk1 = Workbooks("All_data.xls")
Should the code reside on a seperate file?


"Tom Ogilvy" wrote in message
...
it should go something like this.

Dim bk1 as workbook, bk2 as workbook
Dim sh as worksheet, cell as range, rng as Range
Dim rng1 as Range, res as Variant
set bk1 = Workbooks("All_data.xls")
set bk2 = workbooks("Reports.xls")
set rng = bk1.Range(bk1.Cells(2,1),bk1.Cells(2,1).End(xldown ))
for each cell in rng
set sh = Bk2.Worksheets(cell.offset(0,1).value)
set rng1 = sh.Range(sh.cells(2,"H"),sh.Cells(2,"H").End(xldow n))
res = Application.Match(clng(cell.offset(0,7)),rng1,0)
if iserror(res) then
cell.Entirerow.copy
Destination:=rng1.offset(rng1.rows.count,0).Resize (1,1)
end if
Next



--
Regards,
Tom Ogilvy