Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

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

Can I split&write data to each individual sheet?
 

Dear Tom,
I put it in a procedure but still get the compiler error...Unfortunately I
am almost a newbee with VBA programming. Thus I am stuck with my problem.
Help will be appreciated.


"Tom Ogilvy" wrote in message
...
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

Can I split&write data to each individual sheet?
 

I pasted the code from the email in a general module and compiled it. I had
no errors.

--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Dear Tom,
I put it in a procedure but still get the compiler error...Unfortunately I
am almost a newbee with VBA programming. Thus I am stuck with my problem.
Help will be appreciated.


"Tom Ogilvy" wrote in message
...
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

Can I split&write data to each individual sheet?
 

A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the names--I
used Sheet1.

Martyn wrote:

Dear Tom,
I put it in a procedure but still get the compiler error...Unfortunately I
am almost a newbee with VBA programming. Thus I am stuck with my problem.
Help will be appreciated.

"Tom Ogilvy" wrote in message
...
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






--

Dave Peterson

Can I split&write data to each individual sheet?
 

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.

Can I split&write data to each individual sheet?
 

Are you sure that both workbooks are opened when you run the macro?

--
Haldun Alay


"Martyn" , iletide þunu yazdý
...
Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.

Can I split&write data to each individual sheet?
 

Yes Haldun both workbooks are open when the macro is runned.

"Haldun Alay" <haldunalayATyahooDOTcom wrote in message
...
Are you sure that both workbooks are opened when you run the macro?

--
Haldun Alay


"Martyn" , iletide þunu yazdý
...
Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

Can I split&write data to each individual sheet?
 

I put this note in my response:

But you'll have to adjust the name of the worksheet that contains the names--I
used Sheet1.

This is the spot that you specify the workbook and worksheet:
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")


Martyn wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.


--

Dave Peterson

Can I split&write data to each individual sheet?
 

This means that you have used an incorrect name for either the workbook or the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn" wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.

Can I split&write data to each individual sheet?
 

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript out of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.


"Myrna Larson" wrote in message
...
This means that you have used an incorrect name for either the workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn" wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.

Can I split&write data to each individual sheet?
 

put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript out of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in message
...
This means that you have used an incorrect name for either the workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn" wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains the
names--I
used Sheet1.





--

Dave Peterson

Can I split&write data to each individual sheet?
 

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks


"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"
wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains
the
names--I
used Sheet1.





--

Dave Peterson

Can I split&write data to each individual sheet?
 

And you're sure that name matches a worksheet name in wb2?

Maybe leading/trailing/extra spaces????



Jack wrote:

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks

"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"
wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that contains
the
names--I
used Sheet1.





--

Dave Peterson


--

Dave Peterson

Can I split&write data to each individual sheet?
 

Hi Dave,
Yes it looks like the name matches the worksheet name. Would it be a good
idea if I email you the two files and see if you can work it out?. If so,
please let me know which email add. I should use...
Sincerely
J.

"Dave Peterson" wrote in message
...
And you're sure that name matches a worksheet name in wb2?

Maybe leading/trailing/extra spaces????



Jack wrote:

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks

"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript
out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in
message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"
wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the
compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for
me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that
contains
the
names--I
used Sheet1.





--

Dave Peterson


--

Dave Peterson

Can I split&write data to each individual sheet?
 

Before you send anything, what's the name of the worksheet?

And try this:
Open your workbook (all_data.xls--that is the correct name for the workbook???)
then create a new test macro:

option explicit
sub test01()
workbooks("all_data.xls").worksheets("whateveryouc alledit").select
end sub

And post back what happens.



Jack wrote:

Hi Dave,
Yes it looks like the name matches the worksheet name. Would it be a good
idea if I email you the two files and see if you can work it out?. If so,
please let me know which email add. I should use...
Sincerely
J.

"Dave Peterson" wrote in message
...
And you're sure that name matches a worksheet name in wb2?

Maybe leading/trailing/extra spaces????



Jack wrote:

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks

"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names but
although am sure about the one I tried, I also did get a "subscript
out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in
message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"
wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the
compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok for
me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that
contains
the
names--I
used Sheet1.





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can I split&write data to each individual sheet?
 

Yes the test macro seems to work because there is no error message for the
"all_data.xls" file. But the other file "Reports.xls" which contains the
general macro and is to retrive data from "all_data.xls" ...when this is
runned it generates the "subscript out of range" error message.

"Dave Peterson" wrote in message
...
Before you send anything, what's the name of the worksheet?

And try this:
Open your workbook (all_data.xls--that is the correct name for the
workbook???)
then create a new test macro:

option explicit
sub test01()
workbooks("all_data.xls").worksheets("whateveryouc alledit").select
end sub

And post back what happens.



Jack wrote:

Hi Dave,
Yes it looks like the name matches the worksheet name. Would it be a
good
idea if I email you the two files and see if you can work it out?. If
so,
please let me know which email add. I should use...
Sincerely
J.

"Dave Peterson" wrote in message
...
And you're sure that name matches a worksheet name in wb2?

Maybe leading/trailing/extra spaces????



Jack wrote:

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks

"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names
but
although am sure about the one I tried, I also did get a
"subscript
out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in
message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"

wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the
compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok
for
me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that
contains
the
names--I
used Sheet1.





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can I split&write data to each individual sheet?
 

Remember that all_data.xls has to be open before you start the macro. Is it?

This version will yell at you if you don't have it open (remember to change the
sheet name to what you want).

Option Explicit
Sub Tester1()
Dim wb1 As Workbook
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell

End Sub



Jack wrote:

Yes the test macro seems to work because there is no error message for the
"all_data.xls" file. But the other file "Reports.xls" which contains the
general macro and is to retrive data from "all_data.xls" ...when this is
runned it generates the "subscript out of range" error message.

"Dave Peterson" wrote in message
...
Before you send anything, what's the name of the worksheet?

And try this:
Open your workbook (all_data.xls--that is the correct name for the
workbook???)
then create a new test macro:

option explicit
sub test01()
workbooks("all_data.xls").worksheets("whateveryouc alledit").select
end sub

And post back what happens.



Jack wrote:

Hi Dave,
Yes it looks like the name matches the worksheet name. Would it be a
good
idea if I email you the two files and see if you can work it out?. If
so,
please let me know which email add. I should use...
Sincerely
J.

"Dave Peterson" wrote in message
...
And you're sure that name matches a worksheet name in wb2?

Maybe leading/trailing/extra spaces????



Jack wrote:

Hi Dave,
Yes I do see what I expect between the ***'s.
But when I OK pass the msgbox I receive the same old error.
Hope we can solve this puzzle
Thanks

"Dave Peterson" wrote in message
...
put this line right before the offending line:

msgbox "***" & cell.offset(0,1).value & "***"

Do you see what you expected between the ***'s?



Jack wrote:

I think you were correct with your assumption on worksheet names
but
although am sure about the one I tried, I also did get a
"subscript
out
of
range" error for the line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
J.

"Myrna Larson" wrote in
message
...
This means that you have used an incorrect name for either the
workbook or
the
worksheet.

On Wed, 1 Sep 2004 03:55:43 -1200, "Martyn"

wrote:

Thanks Dave,
but I keep getting a "subscript out of range" error from the
compiler
on
line
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
I am stuck. Hope I can find out why...:(
But thank you anyway.
Martyn

"Dave Peterson" wrote in message
...
A couple modifications of Tom's routine and it worked ok
for
me:

Option Explicit

Sub Tester1()
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant
Set wk1 = Workbooks("All_data.xls").Worksheets("sheet1")
Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell
End Sub

But you'll have to adjust the name of the worksheet that
contains
the
names--I
used Sheet1.





--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Can I split&write data to each individual sheet?
 

Hi Dave,
I know I'm taking too much time of yours. Sorry for beeing unsuccessfull
again. I have changed the sheet name to the one I am using on all_data.xls
and It looks like I am opening the correct workbook and worksheet allright
but keep getting the error message at the command line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
The problem may reside on the design/formats of the worksheets/cells I am
using thus may I suggest that I send you the files again please? (if you
kindly will provide me a valid email add.)
Thank you
Jack



"Dave Peterson" wrote in message
...
Remember that all_data.xls has to be open before you start the macro. Is
it?

This version will yell at you if you don't have it open (remember to
change the
sheet name to what you want).

Option Explicit
Sub Tester1()
Dim wb1 As Workbook
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell

End Sub

Can I split&write data to each individual sheet?
 

This sounds like a problem that can be resolved in the newsgroup.

Did you try adding the validation checks into your code?

Jack wrote:

Hi Dave,
I know I'm taking too much time of yours. Sorry for beeing unsuccessfull
again. I have changed the sheet name to the one I am using on all_data.xls
and It looks like I am opening the correct workbook and worksheet allright
but keep getting the error message at the command line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
The problem may reside on the design/formats of the worksheets/cells I am
using thus may I suggest that I send you the files again please? (if you
kindly will provide me a valid email add.)
Thank you
Jack

"Dave Peterson" wrote in message
...
Remember that all_data.xls has to be open before you start the macro. Is
it?

This version will yell at you if you don't have it open (remember to
change the
sheet name to what you want).

Option Explicit
Sub Tester1()
Dim wb1 As Workbook
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell

End Sub

--

Dave Peterson

Can I split&write data to each individual sheet?
 

No, I do not know how to do that.


"Dave Peterson" wrote in message
...
This sounds like a problem that can be resolved in the newsgroup.

Did you try adding the validation checks into your code?

Jack wrote:

Hi Dave,
I know I'm taking too much time of yours. Sorry for beeing unsuccessfull
again. I have changed the sheet name to the one I am using on
all_data.xls
and It looks like I am opening the correct workbook and worksheet
allright
but keep getting the error message at the command line:
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
The problem may reside on the design/formats of the worksheets/cells I
am
using thus may I suggest that I send you the files again please? (if you
kindly will provide me a valid email add.)
Thank you
Jack

"Dave Peterson" wrote in message
...
Remember that all_data.xls has to be open before you start the macro.
Is
it?

This version will yell at you if you don't have it open (remember to
change the
sheet name to what you want).

Option Explicit
Sub Tester1()
Dim wb1 As Workbook
Dim wk1 As Worksheet, bk2 As Workbook
Dim sh As Worksheet, cell As Range, rng As Range
Dim rng1 As Range, res As Variant

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

Set bk2 = Workbooks("Reports.xls")
Set rng = wk1.Range(wk1.Cells(2, 1), wk1.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(xlDown))
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).EntireRow.Cells(1)
End If
Next cell

End Sub

--

Dave Peterson

Can I split&write data to each individual sheet?
 

This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll get a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

Can I split&write data to each individual sheet?
 

Hi,

I guess, One more validation check has to be added to your code as shown below.


.......
.......
For Each cell In rng
on error resume next
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
if sh is nothing then
msgbox "There is no sheet called " & cell.Offset(0, 1).Value & in bk2.name
exit sub
end if

.......
.......


Kind regards.





--
Haldun Alay
"Dave Peterson" , iletide sunu yazdi ...
This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll get a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

Can I split&write data to each individual sheet?
 

Oppps, I missed up something. sorry

The code will be like following.

.......
.......
For Each cell In rng
on error resume next
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
if sh is nothing then
msgbox "There is no sheet called " & cell.Offset(0, 1).Value & " in " & bk2.name
exit sub
end if

.......
.......

kind regards.
--
Haldun Alay



"Haldun Alay" <haldunalayATyahooDOTcom, iletide sunu yazdi ...
Hi,

I guess, One more validation check has to be added to your code as shown below.


......
......
For Each cell In rng
on error resume next
Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)
if sh is nothing then
msgbox "There is no sheet called " & cell.Offset(0, 1).Value & in bk2.name
exit sub
end if

......
......


Kind regards.





--
Haldun Alay
"Dave Peterson" , iletide sunu yazdi ...
This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll get a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

Can I split&write data to each individual sheet?
 

Hi Dave,
I have already tried "all" portions of your code presented on this thread.
As I mentioned earlier, I am not getting any error messages regarding to the
"workbook beeing not opened" or "not having that spesific worksheet". The
"subscript out of range error" is displayed on the:

Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)

line of your code, which I beleive is down passed the control lines you are
reminding.
Hope you & other newsgroup members can help me find out the reason and cure
the problem.
Sincerely
Jack


"Dave Peterson" wrote in message
...
This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll get
a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

Can I split&write data to each individual sheet?
 

I'm sorry, I'm out of guesses.



Jack wrote:

Hi Dave,
I have already tried "all" portions of your code presented on this thread.
As I mentioned earlier, I am not getting any error messages regarding to the
"workbook beeing not opened" or "not having that spesific worksheet". The
"subscript out of range error" is displayed on the:

Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)

line of your code, which I beleive is down passed the control lines you are
reminding.
Hope you & other newsgroup members can help me find out the reason and cure
the problem.
Sincerely
Jack

"Dave Peterson" wrote in message
...
This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll get
a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

--

Dave Peterson

Can I split&write data to each individual sheet?
 

Not many people were interested in solving this problem...
But thank you Dave, you tried to be helpful a lot.

"Dave Peterson" wrote in message
...
I'm sorry, I'm out of guesses.



Jack wrote:

Hi Dave,
I have already tried "all" portions of your code presented on this
thread.
As I mentioned earlier, I am not getting any error messages regarding to
the
"workbook beeing not opened" or "not having that spesific worksheet".
The
"subscript out of range error" is displayed on the:

Set sh = bk2.Worksheets(cell.Offset(0, 1).Value)

line of your code, which I beleive is down passed the control lines you
are
reminding.
Hope you & other newsgroup members can help me find out the reason and
cure
the problem.
Sincerely
Jack

"Dave Peterson" wrote in message
...
This is the portion of code (from a few posts ago) that I suggested:

Set wb1 = Nothing
On Error Resume Next
Set wb1 = Workbooks("all_data.xls")
On Error GoTo 0
If wb1 Is Nothing Then
MsgBox "all_data.xls isn't open!"
Exit Sub
End If

Set wk1 = Nothing
On Error Resume Next
Set wk1 = wb1.Worksheets("sheet1")
On Error GoTo 0
If wk1 Is Nothing Then
MsgBox "all_data.xls doesn't have that sheet"
Exit Sub
End If

If you have spelling mistakes or the workbook isn't open, then you'll
get
a
message box to pop up.

You could go back to the other post and see it in its entirety.



Jack wrote:

No, I do not know how to do that.

<<snipped

--

Dave Peterson