Copy from one sheet to another when criteria satisfied

[columbo1977]

Hi All

I have been trying to get this simple piece of code working and it is getting on my nerves??

Can any one help.

Private Sub CommandButton2_Click()
Dim cl As Range

'For Each cl In [D1:D10]
 If Range("D1") = "Outside NYCC" Then
    Application.ActiveSheet.EntireRow.Select
    Application.ActiveSheet.EntireRow.Copy
    Sheets("Outside NYCC").Select
    Application.ActiveSheet.PasteSpecial
    
 End If
     'Range("D1").Select
    'cl.Worksheet("Outside NYCC").PasteSpecial xlPasteValues
    'cl.PasteSpecial
'Next cl
End Sub

We are trying to test the first sheets to see if D holds Outside NYCC and if it does copy the entire row tot he next sheet.

Any iodeas?

Cheers

Columbo

 

[xlbo]

Don;t use activesheet. You also need an incrementer to tell you which line to paste to if you need to paste more than 1 line. You also need to reference your loopiong variable "cl" as you are just hard coding to check D1 every time at the mo:

Private Sub CommandButton2_Click()
Dim cl As Range, rw as long

 For Each cl In Range("D1:D10")
 If cl = "Outside NYCC" Then
    cl.entirerow.Copy 
    Sheets("Outside NYCC").Range("A" & cl.row).pastespecial xlPasteValues
        
 End If
    
 Next cl
End Sub

not tested...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

Instead of checking row by row, how about using AutoFilter?
Filter "Outside NYCC" on column D, take all of the info and paste to where you want to:

    Selection.AutoFilter Field:=4, Criteria1:="Outside NYCC"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Outside NYCC").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

Have fun.

---- Andy

 

[columbo1977]

Excellent thanks for the replies.

graham