VBA Code Issue

[caos88]

Hello Everyone,

i am trying to get this code working.....but i don't understand where is the error. I have in column U the drop down menu where the word "Completed " should trigger the code. I created a Button, assigned this macro to activate the code...but still nothing.

Sub CutPaste()
Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Completed")

    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("U1:U1000")   ' Do 1000 rows
        If c = "Completed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           Source.Rows(c.Row).Cut Target.Rows(j)
           Target.Range("U" & j).Value = Date
           j = j + 1
               End If
    Next c
End Sub

 

[GWteB]

Try replacing this line

If c = "Completed" Then

with this line

If InStr(1, c.Value2, "completed", vbTextCompare) > 0 Then

 

[caos88]

Hmmm... Works just fine for me.

Based on that, and the fact that the other suggestions don't work for you either, it usually means that:
1. You have left out some important details.
- or -
2. Something is not quite what you think it is (i.e. maybe there is a space BEFORE "Completed" in column U)?
- or -
3. You have disabled events, so none of the Event Procedure code is working.
- or -
4. You have not placed the code in the correct module.

They may be other reasons, but these are the 4 most common issues of this type that I see.

is giving me error here:
Set rng = Intersect(Target, Range("U1:U1000"))

 

[Joe4]

What is exact error message?

Where exactly have you put this code?
Have you put it in the "Sheet1" module?

Also, in your other code, you created a variable named "Target" and used it there. You should not do this. "Target" is a reserved word and using it for names of your variables can cause issues and unexpected results. You should never use reserved words (names of existing functions, properties, methods, and objects) as the name of your variables, procedures, or functions.

If you aren't sure if a word is a reserved word or not, you can do something like simply placing something like "My" in front of it, i.e. "MyTarget".

 

[caos88]

With your code, if you put a break point on this line

Source.Rows(c.Row).Copy Target.Rows(j)

to do that put the cursor anywhere on that line & press F9. Then press F5 does the code stop at the break point?

The entire line is changing color to yellow.

 

[caos88]

Ok Guys.....i just find out that by removing the line where the row supposed to cut, the entire code is working.....so there was something stopping the code on that line
I can manually deleted...unless you can understand where i was wrong

Sub CopyPaste()

Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Completed")

    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("U1:U1000")   ' Do 1000 rows
        If c = "Completed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           Target.Range("V" & j).Value = Date
           j = j + 1
    
        End If
    Next c
End Sub

 

[Fluff]

If you just want to clear the contents of that row use

Source.Rows(c.Row).ClearContents

After you have copied it across.

 

[caos88]

If you just want to clear the contents of that row use

Source.Rows(c.Row).ClearContents

After you have copied it across.

i do have the row copied and the date, yes but on sheet1 the line remains there. i have to delete it manually.

 

[Fluff]

If you want to delete the row use

Sub CopyPaste()

Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Rng As Range


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Completed")

    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("U1:U1000")   ' Do 1000 rows
        If c = "Completed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           If Rng Is Nothing Then Set Rng = c Else Set Rng = Union(Rng, c)
           Target.Range("V" & j).Value = Date
           j = j + 1
    
        End If
    Next c
    If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[caos88]

If you want to delete the row use

Sub CopyPaste()

Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Rng As Range


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Completed")

    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("U1:U1000")   ' Do 1000 rows
        If c = "Completed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           If Rng Is Nothing Then Set Rng = c Else Set Rng = Union(Rng, c)
           Target.Range("V" & j).Value = Date
           j = j + 1
  
        End If
    Next c
    If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

It works !!! Many Thanks @Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.