VBA Code Issue

caos88

Board Regular
Joined
Mar 12, 2020
Messages
61
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
871
Office Version
  1. 2013
Platform
  1. Windows
Try this .....
Rich (BB code):
 Source.Rows(c.Row).EntireRow.Cut Target.Rows(j).EntireRow
 

caos88

Board Regular
Joined
Mar 12, 2020
Messages
61
Office Version
  1. 2010
Platform
  1. Windows
Try this .....
Rich (BB code):
 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 12, 2020
Messages
61
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,406
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
871
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

You're right, what I meant ...
VBA Code:
c.EntireRow.Cut Target.Rows(j)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,406
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,017
Office Version
  1. 365
Platform
  1. Windows
With your code, if you put a break point on this line
VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,539
Messages
5,523,434
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top