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]

I did step thru this code. The row to cut on the "Group Tracking Report" worksheet gets selected and the 'dancing-dashes' appear. The "Completed Group Tracking" worksheet then appears. Cell "A" of first blank line is selected. Next statement is the "ActiveSheet.Paste" which causes the failure.

When I end the debugger the first blank line of the "Completed Group Tracking" is hi-lited. I think I'm losing my cut data but don't know how to tell.

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

 

[SkipVought]

WHY are you using ActiveSheet.Paste? The use of Activate and Select is ill advised.

Skip,

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

 

[Predator98]

Because I don't know alternative methods at this time. I don't know why using Activate and Select is ill advised. I am, as stated many times, very new to this language. I am a mainframe programmer who also happens to use Excel extensively but mainly Excel formulas & functions. I have never before written VB code and have rarely created Excel macros. I will continue my research and see if I can find an alternative method.

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

 

[ck1999]

lets go back to this code and see if it works

     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).copy
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
              .Rows(target.row).delete
           End With
        End If
     End If

ck1999

 

[Gavona]

When I end the debugger the first blank line of the "Completed Group Tracking" is hi-lited.

Sounds like the entire row is selected.

Cell "A" of first blank line is selected

When you select a range of cells one cell in the selected range is active but all are selected.

When you end the debugger, with the workbook active hit Ctrl-v (i.e. perform the paste manually). Do you get an error? Could it be that copy and paste areas are different shapes? Paste to a single cell.

Gavin

 

[Predator98]

I've got it working now. Thanks.

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

 

[Predator98]

Two things: First, I thought it would be a simple task to reverse the process to return items to the original worksheet. The Business requirements state that the item can be returned to active status if updates and/or corrections are needed.

I have included both sets of code below: Code Set 1 works great, Code Set 2 cuts the line but pastes on the very last line of the destination worksheet instead of the first available blank line. It also is not pasting the formatting, just the data values. I don't see why the two sets of code would have such different results.

Secondly, as you can see, I am unprotecting/protecting the worksheets before cut/paste (no problems with this). Question is...is there a way upon protecting the sheet to indicate that users are allowed to use AutoFilter?

 [b]Code Set 1[/b]
     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")
              Sheets("Group Tracking Report").Unprotect
              Sheets("Completed Group Tracking").Unprotect
              .Rows(Target.Row).Copy
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
              .Rows(Target.Row).Delete
              Sheets("Group Tracking Report").Protect
              Sheets("Completed Group Tracking").Protect
           End With
        End If
     End If

 [b]Code Set 2[/b]
     If Not Intersect(Target, Columns("BV")) Is Nothing Then
        If Target.Value = "RETURN TO ACTIVE" And (Cells(Target.Row, "BS").Value <> "" Or Cells(Target.Row, "BT").Value <> "") Then
           With Worksheets("Completed Group Tracking")
              Sheets("Completed Group Tracking").Unprotect
              Sheets("Group Tracking Report").Unprotect
              .Rows(Target.Row).Copy
              Sheets("Group Tracking Report").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
              .Rows(Target.Row).Delete
              Sheets("Completed Group Tracking").Protect
              Sheets("Group Tracking Report").Protect
           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]

if you want the first available blank in column a you can use

 Sheets("Group Tracking Report").Range("A1").End(xldown).Offset(1, 0).PasteSpecial xlPasteAll

instead of

 Sheets("Group Tracking Report").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll

This assumes a1 has information and will find the next row in column a where the cell is empty

I do not know why it is does not paste the format.

ck1999

 

[DrSimon]

Just seen this long tale and really feel for you Predator98. I know it's late in the day, but could you try again with Skip's initial suggestion for your esteemed and obviously highly intelligent manager, but in a slightly different way?

How about a single source worksheet together with In_Progress and Complete sheets? Then write code that recreates In_Progress and Complete whenever the source has been updated. This would mean that the manager has pristine worksheets to give away.

If you want, you could have some more code that deletes the source sheet for his copy - that way he wouldn't know how you'd done it! In fact that might be the best solution: take the two original sheets; combine them into a another empty one; clear the originals and overwrite them from the combined one; delete the combined one.

Just some lateral thinking which you are welcome to completely ignore!

Simon Rouse

 

[Predator98]

I do appreciate the advice. I am still trying to solve the cut/paste back to the "In_Progress" from the "Completed". Still doesn't make any sense why it works one way and not the other. I've tried both the Range("A1").End(xldown) and the Range("A65536").End(xlUp). The A65536 puts it on that actual line. The A1 overwrites row 2 on the worksheet. Did solve the protect issue of allowing the users to use AutoFilter though. If I haven't solved the above mentioned scenario I will be seeking more advice. I'm about to the point of just scrapping the whole dang thing and giving them a couple sheets of paper and a sharp #2 pencil and telling them to do it the old-fashioned way!

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

 

[SkipVought]

The A1 overwrites row 2 on the worksheet."

The clues you gave, indicate that there is a GAP in A1 data, where it "thinks" it's the end of the list.

If column A does not have a value in each and every row where data exists in your table, the End(xlDown) method is NOT acceptable. End(xlUp) will work if there is no extaneous data BELOW you table in column A.

There's almost ALWAYS a caveat.

Skip,

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