Copy row over to new sheet based on cell value

[megaco]

I am working on an excel worksheet where I need a macro that will copy the entire row (A-K) to a new sheet when column A contains the following values:

CCKE

CSUL

SOLV

SPCK

SVCA

SVMX

GSPP

<colgroup><col></colgroup><tbody>
</tbody>

Would appreciate any help!

 

[mumps]

Try:

Sub test()
    Dim Ary As Variant
    Ary = Array("CCKE", "CSUL", "SOLV", "SPCK", "SVCA", "SVMX", "GSPP")
    With Range("A1").CurrentRegion
       .Range("A1:K1").AutoFilter 1, Ary, xlFilterValues
       .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Offset(1, 0)
       .AutoFilter
    End With
End Sub

 

[megaco]

I am working on an excel worksheet where I need a macro that will copy the entire row (A-K) to a new sheet when column A contains the following values:

CCKE

CSUL

SOLV

SPCK

SVCA

SVMX

GSPP

<tbody>
</tbody>

Would appreciate any help!

Thank you so much! this worked!

 

[mumps]

You are very welcome.

 

[megaco]

I was wonder if I get could some help with one additional problem.

I need this macro to add a second sheet first so that it has somewhere to move the information.

Additionally, is there any way to have this apply regardless of the sheet name? Just based on the fact the it is the first and second sheet in the document?

Thanks in advance!!

 

[mumps]

This macro will create a new sheet named "Result" before copying the data to it. Change the name (in red) in the macro to suit your needs.

Sub test()
    Dim Ary As Variant
    Ary = Array("CCKE", "CSUL", "SOLV", "SPCK", "SVCA", "SVMX", "GSPP")
    With Range("A1").CurrentRegion
       .Range("A1:K1").AutoFilter 11, Ary, xlFilterValues
       .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
       Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "[COLOR="#FF0000"]Result[/COLOR]"
       Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
       .AutoFilter
    End With
    Application.CutCopyMode = False
End Sub

 

[jwilliamson]

I'm looking to move a row from one sheet to another sheet when a value in row H is changed to "closed" can I get help with this.

 

[mumps]

@jwilliamson: Welcome to the Forum. According to Forum rules, you should not post your question in another member's thread. Please start your own new thread explaining in detail what you want to do. Include a link to this thread if you feel it is helpful. If you send me a private message with a link to your new thread, I'll be happy to have a look at it.