Hi,

Very new to this so would be grateful for any help.

I'm trying to copy rows from one workbook to another when they meet a
certain criteria. But I do not want to overwrite previously copied
lines. I want to add new rows, that meet the criteria, to the end of
the older copied data.

Dont know whether its relevent but to make sure that lines that are
already on sheet 6 aren't duplicated, I have a lookup function in col
23 of sheet 1 that brings back the unique identifier if it is already
on sheet 6.

I know this is very convaluted!

Sub UnmatchedPOs()

Set rng = Sheets(1).Range("A:V").Rows

Set rngTo = Sheets(6).Range("A1").End(xlDown)

For Each r In rng
If r.Cells(3).Value = "NO" Then
If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
If r.Cells(9).Value < DateValue("April 01, 2008") Then
If Not r.Cells(6).Value = r.Cells(23).Value Then

i = i + 1
r.Copy rngTo(i)

End If
End If
End If
End If
Next

End Sub

When I run this I get:

1004 - Application defined or object defined error

Many Thanks,

Jig

change the following

from:
r.Copy rngTo(i)
to:
r.Copy destination:=rngTo(i)

Or if it is only one cell

rngTo(i).value = r.value


"(E-Mail Removed)" wrote:

> Hi,
>
> Very new to this so would be grateful for any help.
>
> I'm trying to copy rows from one workbook to another when they meet a
> certain criteria. But I do not want to overwrite previously copied
> lines. I want to add new rows, that meet the criteria, to the end of
> the older copied data.
>
> Dont know whether its relevent but to make sure that lines that are
> already on sheet 6 aren't duplicated, I have a lookup function in col
> 23 of sheet 1 that brings back the unique identifier if it is already
> on sheet 6.
>
> I know this is very convaluted!
>
> Sub UnmatchedPOs()
>
> Set rng = Sheets(1).Range("A:V").Rows
>
> Set rngTo = Sheets(6).Range("A1").End(xlDown)
>
> For Each r In rng
> If r.Cells(3).Value = "NO" Then
> If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> If r.Cells(9).Value < DateValue("April 01, 2008") Then
> If Not r.Cells(6).Value = r.Cells(23).Value Then
>
> i = i + 1
> r.Copy rngTo(i)
>
> End If
> End If
> End If
> End If
> Next
>
> End Sub
>
> When I run this I get:
>
> 1004 - Application defined or object defined error
>
> Many Thanks,
>
> Jig
>
>

On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> change the following
>
> from:
> r.Copy rngTo(i)
> to:
> r.Copy destination:=rngTo(i)
>
> Or if it is only one cell
>
> rngTo(i).value = r.value
>
>
>
> "jeegnapat...@hotmail.com" wrote:
> > Hi,
>
> > Very new to this so would be grateful for any help.
>
> > I'm trying to copy rows from one workbook to another when they meet a
> > certain criteria. But I do not want to overwrite previously copied
> > lines. I want to add new rows, that meet the criteria, to the end of
> > the older copied data.
>
> > Dont know whether its relevent but to make sure that lines that are
> > already on sheet 6 aren't duplicated, I have a lookup function in col
> > 23 of sheet 1 that brings back the unique identifier if it is already
> > on sheet 6.
>
> > I know this is very convaluted!
>
> > Sub UnmatchedPOs()
>
> > Set rng = Sheets(1).Range("A:V").Rows
>
> > Set rngTo = Sheets(6).Range("A1").End(xlDown)
>
> > For Each r In rng
> > If r.Cells(3).Value = "NO" Then
> > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > If Not r.Cells(6).Value = r.Cells(23).Value Then
>
> > i = i + 1
> > r.Copy rngTo(i)
>
> > End If
> > End If
> > End If
> > End If
> > Next
>
> > End Sub
>
> > When I run this I get:
>
> > 1004 - Application defined or object defined error
>
> > Many Thanks,
>
> > Jig- Hide quoted text -
>
> - Show quoted text -

Hi Joel,

I still got the above error, with the text you said to put in.

Many Thanks,

Jig

I'm running with excel 2003 and do not get any errors either with the
original code or my modification. I 've tried making some changes to the
worksheets to see if I get the errror and I'm not able to duplicate this
error with your code.

Can you tell me which line is failing?

"Jeegna" wrote:

> On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> > change the following
> >
> > from:
> > r.Copy rngTo(i)
> > to:
> > r.Copy destination:=rngTo(i)
> >
> > Or if it is only one cell
> >
> > rngTo(i).value = r.value
> >
> >
> >
> > "jeegnapat...@hotmail.com" wrote:
> > > Hi,
> >
> > > Very new to this so would be grateful for any help.
> >
> > > I'm trying to copy rows from one workbook to another when they meet a
> > > certain criteria. But I do not want to overwrite previously copied
> > > lines. I want to add new rows, that meet the criteria, to the end of
> > > the older copied data.
> >
> > > Dont know whether its relevent but to make sure that lines that are
> > > already on sheet 6 aren't duplicated, I have a lookup function in col
> > > 23 of sheet 1 that brings back the unique identifier if it is already
> > > on sheet 6.
> >
> > > I know this is very convaluted!
> >
> > > Sub UnmatchedPOs()
> >
> > > Set rng = Sheets(1).Range("A:V").Rows
> >
> > > Set rngTo = Sheets(6).Range("A1").End(xlDown)
> >
> > > For Each r In rng
> > > If r.Cells(3).Value = "NO" Then
> > > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > > If Not r.Cells(6).Value = r.Cells(23).Value Then
> >
> > > i = i + 1
> > > r.Copy rngTo(i)
> >
> > > End If
> > > End If
> > > End If
> > > End If
> > > Next
> >
> > > End Sub
> >
> > > When I run this I get:
> >
> > > 1004 - Application defined or object defined error
> >
> > > Many Thanks,
> >
> > > Jig- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi Joel,
>
> I still got the above error, with the text you said to put in.
>
> Many Thanks,
>
> Jig
>
>

On 9 Nov, 11:40, Joel <J...@discussions.microsoft.com> wrote:
> I'm running with excel 2003 and do not get any errors either with the
> original code or my modification. I 've tried making some changes to the
> worksheets to see if I get the errror and I'm not able to duplicate this
> error with your code.
>
> Can you tell me which line is failing?
>
>
>
> "Jeegna" wrote:
> > On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> > > change the following
>
> > > from:
> > > r.Copy rngTo(i)
> > > to:
> > > r.Copy destination:=rngTo(i)
>
> > > Or if it is only one cell
>
> > > rngTo(i).value = r.value
>
> > > "jeegnapat...@hotmail.com" wrote:
> > > > Hi,
>
> > > > Very new to this so would be grateful for any help.
>
> > > > I'm trying to copy rows from one workbook to another when they meet a
> > > > certain criteria. But I do not want to overwrite previously copied
> > > > lines. I want to add new rows, that meet the criteria, to the end of
> > > > the older copied data.
>
> > > > Dont know whether its relevent but to make sure that lines that are
> > > > already on sheet 6 aren't duplicated, I have a lookup function in col
> > > > 23 of sheet 1 that brings back the unique identifier if it is already
> > > > on sheet 6.
>
> > > > I know this is very convaluted!
>
> > > > Sub UnmatchedPOs()
>
> > > > Set rng = Sheets(1).Range("A:V").Rows
>
> > > > Set rngTo = Sheets(6).Range("A1").End(xlDown)
>
> > > > For Each r In rng
> > > > If r.Cells(3).Value = "NO" Then
> > > > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > > > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > > > If Not r.Cells(6).Value = r.Cells(23).Value Then
>
> > > > i = i + 1
> > > > r.Copy rngTo(i)
>
> > > > End If
> > > > End If
> > > > End If
> > > > End If
> > > > Next
>
> > > > End Sub
>
> > > > When I run this I get:
>
> > > > 1004 - Application defined or object defined error
>
> > > > Many Thanks,
>
> > > > Jig- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi Joel,
>
> > I still got the above error, with the text you said to put in.
>
> > Many Thanks,
>
> > Jig- Hide quoted text -
>
> - Show quoted text -

I'm running Excel 2000 so could that be it?

When I hit debug, the line that is highlighted is the one you asked me
to change. It was that line before I changed it as well.

Cheers,

I couldn't get it to fail. I have no idea. Because this is the only polace
in the code where rngTo is used it probably has something to do with this
range

try adding a line after this instruction to help you find the problem
Set rngTo = Sheets(6).Range("A1").End(xlDown)
new line for testing
rngTo.select

stop the code after this line is executed and see if the rngTo is selected.


"Jeegna" wrote:

> On 9 Nov, 11:40, Joel <J...@discussions.microsoft.com> wrote:
> > I'm running with excel 2003 and do not get any errors either with the
> > original code or my modification. I 've tried making some changes to the
> > worksheets to see if I get the errror and I'm not able to duplicate this
> > error with your code.
> >
> > Can you tell me which line is failing?
> >
> >
> >
> > "Jeegna" wrote:
> > > On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> > > > change the following
> >
> > > > from:
> > > > r.Copy rngTo(i)
> > > > to:
> > > > r.Copy destination:=rngTo(i)
> >
> > > > Or if it is only one cell
> >
> > > > rngTo(i).value = r.value
> >
> > > > "jeegnapat...@hotmail.com" wrote:
> > > > > Hi,
> >
> > > > > Very new to this so would be grateful for any help.
> >
> > > > > I'm trying to copy rows from one workbook to another when they meet a
> > > > > certain criteria. But I do not want to overwrite previously copied
> > > > > lines. I want to add new rows, that meet the criteria, to the end of
> > > > > the older copied data.
> >
> > > > > Dont know whether its relevent but to make sure that lines that are
> > > > > already on sheet 6 aren't duplicated, I have a lookup function in col
> > > > > 23 of sheet 1 that brings back the unique identifier if it is already
> > > > > on sheet 6.
> >
> > > > > I know this is very convaluted!
> >
> > > > > Sub UnmatchedPOs()
> >
> > > > > Set rng = Sheets(1).Range("A:V").Rows
> >
> > > > > Set rngTo = Sheets(6).Range("A1").End(xlDown)
> >
> > > > > For Each r In rng
> > > > > If r.Cells(3).Value = "NO" Then
> > > > > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > > > > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > > > > If Not r.Cells(6).Value = r.Cells(23).Value Then
> >
> > > > > i = i + 1
> > > > > r.Copy rngTo(i)
> >
> > > > > End If
> > > > > End If
> > > > > End If
> > > > > End If
> > > > > Next
> >
> > > > > End Sub
> >
> > > > > When I run this I get:
> >
> > > > > 1004 - Application defined or object defined error
> >
> > > > > Many Thanks,
> >
> > > > > Jig- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi Joel,
> >
> > > I still got the above error, with the text you said to put in.
> >
> > > Many Thanks,
> >
> > > Jig- Hide quoted text -
> >
> > - Show quoted text -
>
> I'm running Excel 2000 so could that be it?
>
> When I hit debug, the line that is highlighted is the one you asked me
> to change. It was that line before I changed it as well.
>
> Cheers,
>
>

On 9 Nov, 12:51, Joel <J...@discussions.microsoft.com> wrote:
> I couldn't get it to fail. I have no idea. Because this is the only polace
> in the code where rngTo is used it probably has something to do with this
> range
>
> try adding a line after this instruction to help you find the problem
> Set rngTo = Sheets(6).Range("A1").End(xlDown)
> new line for testing
> rngTo.select
>
> stop the code after this line is executed and see if the rngTo is selected.
>
>
>
> "Jeegna" wrote:
> > On 9 Nov, 11:40, Joel <J...@discussions.microsoft.com> wrote:
> > > I'm running with excel 2003 and do not get any errors either with the
> > > original code or my modification. I 've tried making some changes to the
> > > worksheets to see if I get the errror and I'm not able to duplicate this
> > > error with your code.
>
> > > Can you tell me which line is failing?
>
> > > "Jeegna" wrote:
> > > > On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> > > > > change the following
>
> > > > > from:
> > > > > r.Copy rngTo(i)
> > > > > to:
> > > > > r.Copy destination:=rngTo(i)
>
> > > > > Or if it is only one cell
>
> > > > > rngTo(i).value = r.value
>
> > > > > "jeegnapat...@hotmail.com" wrote:
> > > > > > Hi,
>
> > > > > > Very new to this so would be grateful for any help.
>
> > > > > > I'm trying to copy rows from one workbook to another when they meet a
> > > > > > certain criteria. But I do not want to overwrite previously copied
> > > > > > lines. I want to add new rows, that meet the criteria, to the end of
> > > > > > the older copied data.
>
> > > > > > Dont know whether its relevent but to make sure that lines that are
> > > > > > already on sheet 6 aren't duplicated, I have a lookup function in col
> > > > > > 23 of sheet 1 that brings back the unique identifier if it is already
> > > > > > on sheet 6.
>
> > > > > > I know this is very convaluted!
>
> > > > > > Sub UnmatchedPOs()
>
> > > > > > Set rng = Sheets(1).Range("A:V").Rows
>
> > > > > > Set rngTo = Sheets(6).Range("A1").End(xlDown)
>
> > > > > > For Each r In rng
> > > > > > If r.Cells(3).Value = "NO" Then
> > > > > > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > > > > > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > > > > > If Not r.Cells(6).Value = r.Cells(23).Value Then
>
> > > > > > i = i + 1
> > > > > > r.Copy rngTo(i)
>
> > > > > > End If
> > > > > > End If
> > > > > > End If
> > > > > > End If
> > > > > > Next
>
> > > > > > End Sub
>
> > > > > > When I run this I get:
>
> > > > > > 1004 - Application defined or object defined error
>
> > > > > > Many Thanks,
>
> > > > > > Jig- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > Hi Joel,
>
> > > > I still got the above error, with the text you said to put in.
>
> > > > Many Thanks,
>
> > > > Jig- Hide quoted text -
>
> > > - Show quoted text -
>
> > I'm running Excel 2000 so could that be it?
>
> > When I hit debug, the line that is highlighted is the one you asked me
> > to change. It was that line before I changed it as well.
>
> > Cheers,- Hide quoted text -
>
> - Show quoted text -

I got an error:

1004 - Select method of range class failed

for the line

rngTo.select

I think you're at the bottom of the worksheet.

I'd add a check to help debug:

i = i + 1
r.Copy rngTo(i)

becomes
if rngto.row = rows.count then
i = i + 1
r.Copy rngTo(i)
else
msgbox "You're out of space!
end if

It could be because you don't have anything below A1 in Sheets(6)--or you have
everything filled in on that column A of that sheet.

(E-Mail Removed) wrote:
>
> Hi,
>
> Very new to this so would be grateful for any help.
>
> I'm trying to copy rows from one workbook to another when they meet a
> certain criteria. But I do not want to overwrite previously copied
> lines. I want to add new rows, that meet the criteria, to the end of
> the older copied data.
>
> Dont know whether its relevent but to make sure that lines that are
> already on sheet 6 aren't duplicated, I have a lookup function in col
> 23 of sheet 1 that brings back the unique identifier if it is already
> on sheet 6.
>
> I know this is very convaluted!
>
> Sub UnmatchedPOs()
>
> Set rng = Sheets(1).Range("A:V").Rows
>
> Set rngTo = Sheets(6).Range("A1").End(xlDown)
>
> For Each r In rng
> If r.Cells(3).Value = "NO" Then
> If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> If r.Cells(9).Value < DateValue("April 01, 2008") Then
> If Not r.Cells(6).Value = r.Cells(23).Value Then
>
> i = i + 1
> r.Copy rngTo(i)
>
> End If
> End If
> End If
> End If
> Next
>
> End Sub
>
> When I run this I get:
>
> 1004 - Application defined or object defined error
>
> Many Thanks,
>
> Jig

--

Dave Peterson

typos!

becomes
if rngto.row = rows.count then
msgbox "You're out of space!"
else
i = i + 1
r.Copy rngTo(i)
end if

Dave Peterson wrote:
>
> I think you're at the bottom of the worksheet.
>
> I'd add a check to help debug:
>
> i = i + 1
> r.Copy rngTo(i)
>
> becomes
> if rngto.row = rows.count then
> i = i + 1
> r.Copy rngTo(i)
> else
> msgbox "You're out of space!
> end if
>
> It could be because you don't have anything below A1 in Sheets(6)--or you have
> everything filled in on that column A of that sheet.
>
> (E-Mail Removed) wrote:
> >
> > Hi,
> >
> > Very new to this so would be grateful for any help.
> >
> > I'm trying to copy rows from one workbook to another when they meet a
> > certain criteria. But I do not want to overwrite previously copied
> > lines. I want to add new rows, that meet the criteria, to the end of
> > the older copied data.
> >
> > Dont know whether its relevent but to make sure that lines that are
> > already on sheet 6 aren't duplicated, I have a lookup function in col
> > 23 of sheet 1 that brings back the unique identifier if it is already
> > on sheet 6.
> >
> > I know this is very convaluted!
> >
> > Sub UnmatchedPOs()
> >
> > Set rng = Sheets(1).Range("A:V").Rows
> >
> > Set rngTo = Sheets(6).Range("A1").End(xlDown)
> >
> > For Each r In rng
> > If r.Cells(3).Value = "NO" Then
> > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > If Not r.Cells(6).Value = r.Cells(23).Value Then
> >
> > i = i + 1
> > r.Copy rngTo(i)
> >
> > End If
> > End If
> > End If
> > End If
> > Next
> >
> > End Sub
> >
> > When I run this I get:
> >
> > 1004 - Application defined or object defined error
> >
> > Many Thanks,
> >
> > Jig
>
> --
>
> Dave Peterson

--

Dave Peterson

I only get the error if there is no data in column A on sheet 6 the 1st time
I call the code. If I put data into column A the error stops occuring. Then
if I remove the data from column A the error doesn't return.

I don't recommend using indexing to reference worksheets. Use the actual
name of the worksheet like
Sheets("Sheet1") not Sheets(1). The index refers to the ordering the sheets
appear in the workbook. You can move the order or the worksheets by sliding
the tab on the vbottom of the worksheet. I think the problem may be that the
sheet you are calling sheet(6) is not the 6th sheet in the workbook but some
other sheet.

"Jeegna" wrote:

> On 9 Nov, 12:51, Joel <J...@discussions.microsoft.com> wrote:
> > I couldn't get it to fail. I have no idea. Because this is the only polace
> > in the code where rngTo is used it probably has something to do with this
> > range
> >
> > try adding a line after this instruction to help you find the problem
> > Set rngTo = Sheets(6).Range("A1").End(xlDown)
> > new line for testing
> > rngTo.select
> >
> > stop the code after this line is executed and see if the rngTo is selected.
> >
> >
> >
> > "Jeegna" wrote:
> > > On 9 Nov, 11:40, Joel <J...@discussions.microsoft.com> wrote:
> > > > I'm running with excel 2003 and do not get any errors either with the
> > > > original code or my modification. I 've tried making some changes to the
> > > > worksheets to see if I get the errror and I'm not able to duplicate this
> > > > error with your code.
> >
> > > > Can you tell me which line is failing?
> >
> > > > "Jeegna" wrote:
> > > > > On 8 Nov, 18:44, Joel <J...@discussions.microsoft.com> wrote:
> > > > > > change the following
> >
> > > > > > from:
> > > > > > r.Copy rngTo(i)
> > > > > > to:
> > > > > > r.Copy destination:=rngTo(i)
> >
> > > > > > Or if it is only one cell
> >
> > > > > > rngTo(i).value = r.value
> >
> > > > > > "jeegnapat...@hotmail.com" wrote:
> > > > > > > Hi,
> >
> > > > > > > Very new to this so would be grateful for any help.
> >
> > > > > > > I'm trying to copy rows from one workbook to another when they meet a
> > > > > > > certain criteria. But I do not want to overwrite previously copied
> > > > > > > lines. I want to add new rows, that meet the criteria, to the end of
> > > > > > > the older copied data.
> >
> > > > > > > Dont know whether its relevent but to make sure that lines that are
> > > > > > > already on sheet 6 aren't duplicated, I have a lookup function in col
> > > > > > > 23 of sheet 1 that brings back the unique identifier if it is already
> > > > > > > on sheet 6.
> >
> > > > > > > I know this is very convaluted!
> >
> > > > > > > Sub UnmatchedPOs()
> >
> > > > > > > Set rng = Sheets(1).Range("A:V").Rows
> >
> > > > > > > Set rngTo = Sheets(6).Range("A1").End(xlDown)
> >
> > > > > > > For Each r In rng
> > > > > > > If r.Cells(3).Value = "NO" Then
> > > > > > > If r.Cells(9).Value >= DateValue("April, 01, 2007") Then
> > > > > > > If r.Cells(9).Value < DateValue("April 01, 2008") Then
> > > > > > > If Not r.Cells(6).Value = r.Cells(23).Value Then
> >
> > > > > > > i = i + 1
> > > > > > > r.Copy rngTo(i)
> >
> > > > > > > End If
> > > > > > > End If
> > > > > > > End If
> > > > > > > End If
> > > > > > > Next
> >
> > > > > > > End Sub
> >
> > > > > > > When I run this I get:
> >
> > > > > > > 1004 - Application defined or object defined error
> >
> > > > > > > Many Thanks,
> >
> > > > > > > Jig- Hide quoted text -
> >
> > > > > > - Show quoted text -
> >
> > > > > Hi Joel,
> >
> > > > > I still got the above error, with the text you said to put in.
> >
> > > > > Many Thanks,
> >
> > > > > Jig- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > I'm running Excel 2000 so could that be it?
> >
> > > When I hit debug, the line that is highlighted is the one you asked me
> > > to change. It was that line before I changed it as well.
> >
> > > Cheers,- Hide quoted text -
> >
> > - Show quoted text -
>
> I got an error:
>
> 1004 - Select method of range class failed
>
> for the line
>
> rngTo.select
>
>