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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
874
Office Version
  1. 2013
Platform
  1. Windows
Try replacing this line
Rich (BB code):
If c = "Completed" Then


with this line
VBA Code:
If InStr(1, c.Value2, "completed", vbTextCompare) > 0 Then
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

caos88

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

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

Board Regular
Joined
Mar 12, 2020
Messages
61
Office Version
  1. 2010
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?
The entire line is changing color to yellow.
 

caos88

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

ADVERTISEMENT

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

VBA Code:
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
If you just want to clear the contents of that row use
VBA Code:
Source.Rows(c.Row).ClearContents
After you have copied it across.
 

caos88

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

ADVERTISEMENT

If you just want to clear the contents of that row use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
If you want to delete the row use
VBA Code:
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

Board Regular
Joined
Mar 12, 2020
Messages
61
Office Version
  1. 2010
Platform
  1. Windows
If you want to delete the row use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,560
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top