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 this .....

 Source.Rows(c.Row).EntireRow.Cut Target.Rows(j).EntireRow

 

[caos88]

Try this .....

 Source.Rows(c.Row).EntireRow.Cut Target.Rows(j).EntireRow

Hello,

Thanks for replying...no, it doesn't copy or cut and this part of the code is coming Yellow now, like an error.

 

[Fluff]

There is no need to use EntireRow with Rows, as Rows is the entire row.
Does Complete have a space after it & does it have a capital C?

 

[caos88]

There is no need to use EntireRow with Rows, as Rows is the entire row.
Does Complete have a space after it & does it have a capital C?

Hello,
No Space after Completed and yes, it's a Capital C

 

[Joe4]

I would probably approach this a different way, and instead create a "Worksheet_Change" event procedure that runs off of changes in column U of Sheet1.
So, if you right-click on the sheet tab name at the bottom of the sheet and select "View Code", paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim srcSheet As Worksheet
    Dim dstSheet As Worksheet
    Dim j As Long

'   Set ws variables
    Set srcSheet = ActiveWorkbook.Worksheets("Sheet1")
    Set dstSheet = ActiveWorkbook.Worksheets("Completed")
        
'   See if entry made in watched cell
    Set rng = Intersect(Target, Range("U1:U1000"))
    
'   Exit if update not in column U
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in column U
    For Each cell In rng
        If cell.Value = "Completed" Then
            Application.EnableEvents = False
'           Find last row on new sheet
            j = dstSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
'           Cut and paste data to completed sheet
            srcSheet.Rows(cell.Row).Cut dstSheet.Rows(j)
'           Update column U with date
            dstSheet.Range("U" & j).Value = Date
            Application.EnableEvents = True
        End If
    Next cell
    
End Sub

This will run automatically whenever column U is changed to "Completed".

One question, did you intend to leave a blank row on "Sheet1" after the Cut/Paste, or did you want to delete that row after the move?

 

[GWteB]

There is no need to use EntireRow with Rows, as Rows is the entire row.

You're right, what I meant ...

c.EntireRow.Cut Target.Rows(j)

 

[caos88]

You're right, what I meant ...

c.EntireRow.Cut Target.Rows(j)

Doesn't work.....@Joe4

 

[Joe4]

Doesn't work.....@Joe4

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.

 

[Fluff]

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?