Copy row over to new sheet based on cell value

megaco

New Member
Joined
Mar 12, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,552
Try:
Code:
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

New Member
Joined
Mar 12, 2019
Messages
4
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!
 

megaco

New Member
Joined
Mar 12, 2019
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2012
Messages
9,552
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.
Code:
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

New Member
Joined
Apr 1, 2019
Messages
7
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,552
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,906
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top