Move Excel row to new worksheet 3

[Predator98]

I have an Excel workbook with two (2) worksheets which are named In_Progress and Complete. The formats, formulas, data validation, etc. are identical within each worksheet. In one column is a dropdown with multiple selections including 'Completed' and 'NeedsUpdate'. While in the In_Progress worksheet if 'Completed' is selected I want to "move" (cut) the active row and paste the cut row in the first available row in the Complete worksheet. On the opposite side of this coin...if in the Complete worksheet and NeedsUpdate is selected I want to move (cut) the active row and paste it back to the In_Progress worksheet in the first available row. We are currently using MS Excel 2003.

I have already testified that I am very much the "rookie" here and even describing this has gone over my head!!! Any assistance would be greatly appreciated. I have made exhaustive searches and have yet to find anything even close.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[Predator98]

So, are you saying the statement should now look like this:

.Cells(Activecell.Row, 1).End(xlUp).Offset(1).EntireRow.Copy

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

.Cells(Activecell.Row, 1).End(xlUp).EntireRow.Copy

the .End(xlup) get you to the last row of data.
.offset(1) get you one row PAST the last row of data.

That's why I suggested that you observe what happens using Activate and Select. Help yourselt out, man!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Predator98]

As I stated when I first came in here...I am very new to VBA coding. I am not familiar with statement structure, objects, methods, syntax, etc. I have winged my way through with what I have done thus far...using examples found on the internet, forums, etc. I have stepped through using the Activate/Select as suggested but still do not see where it is incorrect. I'm sorry you don't seem to have the patience for a true novice's questions. I will search the internet some more, visit other forums, Excel help, and attempt to figure this out.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

It does frustrate me when a suggestion is made and then ignored. I'll stick with you if you can do some of the heavy lifting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Predator98]

Below is current code. Data entered on "In_Progess" worksheet. First available row on "Completed" worksheet is row 53. In stepping through all statements are executed and destination ends up on row 53 of the "Completed" sheet. I attempted a statement to paste but it still only pasted the formatting and through off the delete (ended up on row 2056 in the "In_Progress" worksheet)...the statement I coded was ".Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.PasteSpecial xlPasteAll".

If Target.Value = "COMPLETED" And (Cells(Target.Row, "BS").Value <> "" Or Cells(Target.Row, "BT").Value <> "") Then
With Worksheets("Group Tracking Report")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.Copy
Sheets("Completed Group Tracking").Activate
Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).Select
' .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.Delete
End With
End If

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[Predator98]

And...I do not know how to insert the code in a "Code" box in a submission or I would send it to you that way.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

"And...I do not know how to insert the code in a "Code" box in a submission or I would send it to you that way."

Search for [blue]Process TGML[/blue] on this page.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ck1999]

This conversation is getting painful!

Predator98,

What skip is trying to get you to understand in your code is what is your code doing? You are not following the lines of code to try and understand the code. Your original code

    If Range("BV" & Target.Row).Value = "COMPLETED" And (Range("BS" & Target.Row).Value <> "") Or (Range("BT" & Target.Row).Value <> "") Then
       Application.ScreenUpdating = False
       Worksheets("Group Tracking Report").Select
       ActiveCell.EntireRow.Select
       ActiveCell.EntireRow.Cut
       Sheets("Completed Group Tracking").Select
       Range("A65536").End(xlUp).Offset(1, 0).Select
       ActiveSheet.Paste
       Application.CutCopyMode = False
       Sheets("Group Tracking Report").Select
       Selection.Delete Shift:=xlUp
       ActiveCell.Select
       Application.ScreenUpdating = True
     End If

uses activecell.entirerow

your new code uses

        If Target.Value = "COMPLETED" And (Cells(Target.Row, "BS").Value <> "" Or Cells(Target.Row, "BT").Value <> "") Then
           With Worksheets("Group Tracking Report")
              .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.Copy
              Sheets("Completed Group Tracking").Activate
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).Select
              ' .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.Delete
           End With
        End If

this uses

 .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(0).EntireRow.Copy [/code
to determine which code to copy 

Skip earlier told you this line of code copies a blank line.

So Predator the question now for you is what do you want to copy?  a blank line? your activecell row? or your target.row

By stepping through the code you should be able to determine which row is being copied (watch the moving dashes).  

This forum is for helping out and showing direction!

ck1999

 

[SkipVought]

...and rather than handing you a rote answer, helping you to develop a process for understanding & debugging your code, which is alot faster than having to ask questions in a forum, if you have that skill.

Give a man a fish and he eats for a day. Teach a man to fish and he eats for a lifetime.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I suggested to someone at work that they stepped through their code but that didn't help them. When I looked over their shoulder I discovered that the VBE was maximised so that as they stepped through they could not see what was going on in the worksheet! So Predator98, apologies if this is obvious, but size the VBE screen so you can see what is going on in your worksheet as you step through the code.


Gavin

 

[Predator98]

Thanks, Gavona but I tried that. I do have it working now...at least the cutting and pasting...

     If Not Intersect(Target, Columns("BV")) Is Nothing Then
        If Target.Value = "COMPLETED" And (Cells(Target.Row, "BS").Value <> "" Or Cells(Target.Row, "BT").Value <> "") Then
           With Worksheets("Group Tracking Report")
              Rows(Target.Row).Select
              Selection.Cut
              Sheets("Completed Group Tracking").Select
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(0, 0).Select
              ActiveSheet.Paste
           End With
        End If
     End If

The only thing I can't seem to find anything about (as of yet anyway)...is how to, once the row is cut, actually make the following rows shift up. Once the cut is made it leaves a blank, non-formatted row. Is there some trick to doing this?

Thanks for everyone's assistance and patience.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

Hence the COPY, PASTE and Delete Shift:=xlUp

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Predator98]

I am at a loss here. I saw this work yesterday, even demonstrated it to several co-workers...but now I get the following:

Run-time error '1004': Paste method of Worksheet class failed.

The line of code that is indicated is hi-lited below. Any suggestions?

     If Not Intersect(Target, Columns("BV")) Is Nothing Then
        If Target.Value = "COMPLETED" And (Cells(Target.Row, "BS").Value <> "" Or Cells(Target.Row, "BT").Value <> "") Then
           With Worksheets("Group Tracking Report")
              Rows(Target.Row).Select
              Selection.Cut
              Sheets("Completed Group Tracking").Select
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).Select
              Sheets("Completed Group Tracking").Activate
              [highlight]ActiveSheet.Paste[/highlight]
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).Select
              Sheets("Group Tracking Report").Select
              Selection.Delete Shift:=xlUp
              Sheets("Group Tracking Report").Range("A65536").End(xlUp).Offset(1, 0).Select
           End With
        End If
     End If

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[ck1999]

Could the completed group tracking sheet be protected?


ck1999

 

[Predator98]

Nope. Both worksheets are unprotected. Encountered that error before and unprotected the worksheets.

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[SkipVought]

It is a very dangerous strategy to merely activate another sheet and then just paste, not knowing WHERE (what range) is selected.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Predator98]

I changed the order of the statements as follows:

              Sheets("Completed Group Tracking").Select
              Sheets("Completed Group Tracking").Activate
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).Select
              ActiveSheet.Paste

...still received error. Is there some sort of reference where run-time errors & descriptions can be found?

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[Predator98]

Could the problem be that I am losing what is in the clipboard? I have attempted changing the paste to ActiveSheet.PasteSpecial and to Selection.Paste...both versions receive the same error.

In all instances the "Completed" sheet is slected and the first available row is hi-lited but it seems that there is nothing to Paste. Am I way off base here? Even in the ball park?

If you don't think you can do somethin' it's purty near a cinch you ain't gonna!

 

[ck1999]

Step through your code and see if after Selection.Cut what is actually being cut. you may need to add
Sheets("Completed Group Tracking").select before Rows(Target.Row).Select
You have not told it what worksheet to cut from (atleast not in the code you posted)

ck1999

 

[SkipVought]

Do you have EVENT coding on the Complete sheet? The SELECTING in that sheet COULD "cancel" the COPY.

BTW, I only suggested using Activate and Select in order to OBSERVE what was happening with your code. I in NO WAY endorse using Activate and Select in this code, for the actual Copy 'n' Paste.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}