Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

"Eucalypta" wrote:

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world

Hi Joel,
Thanks for your help.
If I type the macro below, I get errors: my version of Excel (2003) does not
understand .Range etc. The help function tells me "An identifier beginning
with a period is valid only within a With block." Should I use something else?
KG, Eucalypta

"Joel" wrote:

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

"Eucalypta" wrote:

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world

The with block is Activesheet. the code is using the activesheet as the
source and Sheet2 as the destination. The error is probably because you
don't have a sheet2. Change "Sheet2" to match the names of your worksheets.
You may want to change
with activesheet to match you sheet name
from
with activesheet
to
with sheets("MysheetName")

"Eucalypta" wrote:

Hi Joel,
Thanks for your help.
If I type the macro below, I get errors: my version of Excel (2003) does not
understand .Range etc. The help function tells me "An identifier beginning
with a period is valid only within a With block." Should I use something else?
KG, Eucalypta

"Joel" wrote:

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

"Eucalypta" wrote:

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world

Hi Joel,
I have retyped the macro and it runs oke now. That is, without giving
warnings. It does not however copy anything although criteria are met. I have
changed the sheetnames too.
Awaiting your kind input.
KG, Eucalypta

"Joel" wrote:

The with block is Activesheet. the code is using the activesheet as the
source and Sheet2 as the destination. The error is probably because you
don't have a sheet2. Change "Sheet2" to match the names of your worksheets.
You may want to change
with activesheet to match you sheet name
from
with activesheet
to
with sheets("MysheetName")

"Eucalypta" wrote:

Hi Joel,
Thanks for your help.
If I type the macro below, I get errors: my version of Excel (2003) does not
understand .Range etc. The help function tells me "An identifier beginning
with a period is valid only within a With block." Should I use something else?
KG, Eucalypta

"Joel" wrote:

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

"Eucalypta" wrote:

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world

I think the problem is with this line of code
from
If IsDate(.Range("A" & OldRowCount)) Then
to
If IsDate(.Range("B" & OldRowCount)) Then

If above doesn't work then the usual problems with dates is that they are
not in date format instead the are actually text. One way to determine the
format is to click on a cell with the date then go to the menu Format - Cell
- Numbers and then check if a date format or text format is highlighted.
usually if it is dates, then a sinhgle quote is in front of the date and
should be removed .

You may need to step through the code to determine the problem. Add break
points by pressing line of code and then pressing F9. You can also single
step through the code by press F8.



"Eucalypta" wrote:

Hi Joel,
I have retyped the macro and it runs oke now. That is, without giving
warnings. It does not however copy anything although criteria are met. I have
changed the sheetnames too.
Awaiting your kind input.
KG, Eucalypta

"Joel" wrote:

The with block is Activesheet. the code is using the activesheet as the
source and Sheet2 as the destination. The error is probably because you
don't have a sheet2. Change "Sheet2" to match the names of your worksheets.
You may want to change
with activesheet to match you sheet name
from
with activesheet
to
with sheets("MysheetName")

"Eucalypta" wrote:

Hi Joel,
Thanks for your help.
If I type the macro below, I get errors: my version of Excel (2003) does not
understand .Range etc. The help function tells me "An identifier beginning
with a period is valid only within a With block." Should I use something else?
KG, Eucalypta

"Joel" wrote:

Worksheet functions will not move data from one sheet to another. You can
only reference other cells with worksheet functions

You need a macro to actually move the data. the macro below should do the job

Sub moveitems()

With ActiveSheet
OldRowCount = 1
NewRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If IsDate(.Range("A" & OldRowCount)) Then
If (Date - .Range("B" & OldRowCount)) 60 And _
.Range("L" & OldRowCount) = "" Then

.Range("E" & OldRowCount & ":G" & OldRowCount).Copy
With Sheets("Sheet2")
.Range("A" & NewRowCount).Paste
NewRowCount = NewRowCount + 1
End With
End If
End If
OldRowCount = OldRowCount + 1
Loop
End With
End Sub

"Eucalypta" wrote:

Dear reader,
I kindly request your help with the following:
I want Excel to lookup multiple values i(col. E, F and G) in a row and copy
them to a separate worksheet if criteria are met.
Worksheet:
Col.B Col.E Col.F Col.G Col.H Col.L
Date Art. # # Pieces Price Total Order#
11-01-08 411-00-7 40.000 3,15 126.000
Criteria:
If the date in col. B is at least two months later and col. L is empty, I
want Excel to copy the contents of col. E-F-G to a separate worksheet.

I have tried lookup and if, but do not seem to be able to tell Excel what I
want. Can anyone help me, please? I am using Excel 2003.

Kind regards,
Eucalypta
--
A smile will carry you around the world