if cell = no how to copy to another sheet

O

oasisoflife

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
 
C

Carim

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
 
M

Max

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(SMALL($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.
 
O

oasisoflife

Max said:
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(SMALL($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.
Click to expand...


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