if cell = no how to copy to another sheet
 

Can anyone help .I should have done this in a database to start with,
however it’s a working spreadsheet that’s growing all the time.
Basically it’s a tally of what I have sold. the problem I have is if
the customer has paid or not I have the text yes or no in a cell .
what I would like to do is If cell =”No” then copy the complete row
“Name, date, Product etc into another sheet that I will call unpaid .
has anyone got any ideas as to how I can achieve this . so far I have
tried auto filter and macros . I can do it manually by sorting then
copying. I Would appreciate any advice please.
Thanks Paul

if cell = no how to copy to another sheet
 

Hi Paul,

Adjust the following your specific needs :

Sub Macro1()
Dim Paid As String
Application.Goto ("R1C1")
ActiveCell.Range("C1").Select ' adjust to your column
Do
Paid = ActiveCell.Value
Select Case Paid
Case Is = No
ActiveCell.EntireRow.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
Case Else
ActiveCell.Offset(1, 0).Select
End Select
Loop Until ActiveCell.Value = ""
Application.Goto ("R1C1")
End Sub

HTH
Cheers
Carim

if cell = no how to copy to another sheet
 

Another option which uses non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X, from row2 down in cols A to D, with col
D = key col (labelled: Paid?) housing either values: Yes, No or nothing
(empty)

In a new sheet: Y (say),
Create* a DV droplist in D1 to allow selection of the key col's value, ie
either: Yes or No. *via Data Validation , Allow: List, Source: Yes, No

Put in A2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(X!A:A,MATCH(SMAL L($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

Put in D2:
=IF(X!D2="","",IF(X!D2=$D$1,ROW(),""))

Then just select A2:D2 and copy down to cover the max expected extent of
data in X. Cols A to C will return the required results dynamically from X,
eg only the lines with "No" in col D if the value selected in D1 is "No", or
only the lines with "Yes" in col D if the value selected in D1 is "Yes". All
results will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"oasisoflife" wrote:
Can anyone help .I should have done this in a database to start with,
however its a working spreadsheet thats growing all the time.
Basically its a tally of what I have sold. the problem I have is if
the customer has paid or not I have the text yes or no in a cell .
what I would like to do is If cell =€�No€� then copy the complete row
€œName, date, Product etc into another sheet that I will call unpaid .
has anyone got any ideas as to how I can achieve this . so far I have
tried auto filter and macros . I can do it manually by sorting then
copying. I Would appreciate any advice please.
Thanks Paul

if cell = no how to copy to another sheet
 

Max wrote:
Another option which uses non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X, from row2 down in cols A to D, with col
D = key col (labelled: Paid?) housing either values: Yes, No or nothing
(empty)

In a new sheet: Y (say),
Create* a DV droplist in D1 to allow selection of the key col's value, ie
either: Yes or No. *via Data Validation , Allow: List, Source: Yes, No

Put in A2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(X!A:A,MATCH(SMAL L($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

Put in D2:
=IF(X!D2="","",IF(X!D2=$D$1,ROW(),""))

Then just select A2:D2 and copy down to cover the max expected extent of
data in X. Cols A to C will return the required results dynamically from X,
eg only the lines with "No" in col D if the value selected in D1 is "No", or
only the lines with "Yes" in col D if the value selected in D1 is "Yes". All
results will be neatly bunched at the top.


Thanks a Lot Chaps I think I can work something out now with the
information you have given .

if cell = no how to copy to another sheet
 

You're welcome ! Thanks for calling back ..

Here's a working sample to complement the suggested construct:
http://savefile.com/files/9241953
AutoCopy lines into new sht based on key col value
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"oasisoflife" wrote:
... Thanks a Lot Chaps I think I can work something out now
with the information you have given .

if cell = no how to copy to another sheet
 

Max wrote:
You're welcome ! Thanks for calling back ..

Here's a working sample to complement the suggested construct:
http://savefile.com/files/9241953
AutoCopy lines into new sht based on key col value
Max your a star thats perfect , thanks again
Paul

if cell = no how to copy to another sheet
 

Paul, you're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"oasisoflife" wrote:
.. Max your a star thats perfect , thanks again
Paul