Macro Help

[amwalo]

I am trying to make my code work on the entire column by clicking a button. I am trying to get it to work with out having to highlight and having the one click take car of the entire column if that makes sense. The code deletes an entire row with completed in the column and moves it to another sheet. Side question is there a way to have it work by just hitting enter after entering completed?

Here is my code:

'VBA Code by Scott from SpreadsheetPlanet.com
Sub move_rows_to_another_sheet()
For Each mycell In Selection.Columns(9).Cells
If mycell.Value = "Completed" Then
mycell.EntireRow.Copy Worksheets("Completed").Range("A" & Rows.Count).End(3)(2)
mycell.EntireRow.Delete
End If

Next

End Sub

 

[Candyman8019]

Try changing this:
For Each mycell In Selection.Columns(9).Cells
to this:
For Each mycell In columns("I1:I1000").select

NOTE: Change the I1000 to however many rows you have in the sheet.

You can have it run automatically. On the sheet where you would type completed, add the following:

Private Sub Worksheet_Change(ByVal target As Range)

 If Not Intersect(Range("I1:I1000"), target) Is Nothing Then
   If target.Value = "Completed" Then

 'run your macro here

   End If

End If
End Sub

 

[amwalo]

Try changing this:
For Each mycell In Selection.Columns(9).Cells
to this:
For Each mycell In columns("I1:I1000").select

NOTE: Change the I1000 to however many rows you have in the sheet.

You can have it run automatically. On the sheet where you would type completed, add the following:

Private Sub Worksheet_Change(ByVal target As Range)

 If Not Intersect(Range("I1:I1000"), target) Is Nothing Then
   If target.Value = "Completed" Then

 'run your macro here

   End If

End If
End Sub

When I changed the line it comes up with a application-defined or object defined error. I added the other code to the specific sheet so we will see if that works.

 

[Candyman8019]

Right...rather than using the select, try using the range:

For Each mycell in range("I1:I1000")

 

[amwalo]

When I changed the line it comes up with a application-defined or object defined error. I added the other code to the specific sheet so we will see if that works.

Do I need to change anything about my code to add it to the VBA code you supplied for the worksheet?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("I:I")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If target = "Completed" Then
        target.EntireRow.Copy Worksheets("Completed").Range("A" & Rows.Count).End(3)(2)
        target.EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub

 

[amwalo]

Right...rather than using the select, try using the range:

For Each mycell in range("I1:I1000")

Okay so it will pull two of the rows but no more than that

 

[amwalo]

Try:

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("I:I")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If target = "Completed" Then
        target.EntireRow.Copy Worksheets("Completed").Range("A" & Rows.Count).End(3)(2)
        target.EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub

So it says type mismatch on the If target = "Completed" Then. Thank you for helping me lol I have been trying to figure this out for two days now.

 

[Candyman8019]

try:
if target.value = "Completed" then

 

[amwalo]

if target.value = "Completed" then

Still mismatch lol