VBA - Copy/Paste Values if Cell equals Value

trock12

New Member
Joined
Dec 17, 2014
Messages
29
I can't quite nail this one down with VBA.

What I'm trying to accomplish is:

For a collection of rows (2:205), if the cell value in column H equals "Y" - copy and paste the values of that entire row. If the value of column H equals "N" or is blank - then do nothing to the row.

Any push in the right direction is appreciated!
 
Last edited:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
As long as you have a header row in row1 you could use either Autofilter or Advanced filter
 

trock12

New Member
Joined
Dec 17, 2014
Messages
29
I tried using Autofilter, but in the VBA it kept referring to that as a "Selection" and would error out when running the macro.

Code:
ActiveSheet.Range("$A$1:$G$205").AutoFilter Field:=7, Criteria1:="Y"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xltoRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
I didn't realise that you were looking to convert to values, rather than copy/paste to a new sheet.
In that case try
Code:
Sub trock12()
   Dim Rng As Range
   With ActiveSheet
      .Range("A1:G205").AutoFilter 7, "Y"
      For Each Rng In .AutoFilter.Range.SpecialCells(xlVisible).Areas
         Rng.Value = Rng.Value
      Next Rng
      .AutoFilterMode = False
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,101,934
Messages
5,483,769
Members
407,409
Latest member
natshafernandes

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top