MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 336|Reply: 3

VBA move a row based on a "YES" answer.

[Copy link]

2

Topics

3

Posts

12

Integral

Member

Rank: 2

Integral
12
Post on 2-12-2017 01:27:08 | All posts |Read mode
Hi There,
I have a small table of data. I want the row to be put into a second sheet when column F has a YES input into it. I would also like sheet1 to NOT leave the row blank, in other words, for the rows to all move up so there are no blank ones. The following is an example I pulled from a site somewhere which looked like it should be perfect! The person there had the identical problem to myself:
Right Click the sheet tab for the sheet where you will be entering Yes.
Choose View Code.
Paste this code into the pane that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
  If Target = Yes Then
    Application.EnableEvents = False
      nxtRow = Sheets(Completed).Range(I  Rows.Count).End(xlUp).Row + 1
       Target.EntireRow.Copy _
        Destination:=Sheets(Completed).Range(A  nxtRow)
       Target.EntireRow.Delete
  End If
End If
Application.EnableEvents = True
End Sub


I made the necessary changes to the above based on my own parameters such as column I became column F I also renamed my sheets to current and completed, and I changed yes to YES' to match my column F.
Nothing happened? There was no data taken from sheet 1 with the YES onto sheet2 completed. Sheet 2 was blank, and sheet 1 was the same. I'm not sure if I didn't save it properly? I also saved it in the .xlsm format. If anyone can offer help, it'd be appreciated.
Thanks!
AS




Reply

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-12-2017 02:48:39 | All posts



Change the column number 9 to column number 6 (column F).
If Target.Column =
[color=]6
Then











Reply Support Opposition

Props Report

2

Topics

3

Posts

12

Integral

Member

Rank: 2

Integral
12
 Author| Post on 2-12-2017 04:27:59 | All posts



Yes, I did that also...I forgot to mention it, sorry.




Reply Support Opposition

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-12-2017 05:46:34 | All posts



Maybe the worksheet events were disabled. Put this macro in a standard code mode and run it once. Then test your code.
Sub ReEnable()
Application.EnableEvents = True
End SubIf that doesn't work, attach an example workbook with your code.
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-19-2017 16:15 , Processed in 0.118005 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!