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!

 

[SkipVought]

Why do you actually need to CUT the data? Not usually a good strategy.

It seems to me, that a simple PivotTable might do the job, or a query, via MS Query.

Skip,

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

 

[Predator98]

Not my call in this instance. The Business Unit manager uses the two separate worksheets to report to the execs. I have suggested filtering but have been told I don't get paid to suggest...I get paid to "do". The worksheets contain projected (In_Progess) and actual (Completed) monetary amounts that are used in profit projections and analysis.

Sorry, wish it were simpler. And, just for my edification, why is this not deemed a good strategy?

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

 

[SkipVought]

Wow! I am blessed in the job I hold! My manager give me requirements, and does not ignorantly dictate HOW to make it happen.

Simple. Insert a Query on each sheet using the appropriate criteria. Data > Refresh each time a new set of data is required.

"And, just for my edification, why is this not deemed a good strategy?"

Generally, source data is a corporate asset, best maintained in a well designed, NORMALIZED format. To chop similar data up into segments, destroying the source, can be a travesty.

Not knowing the structure or reson for the data, makes it difficult to make specific suggestions. It seems that this is data related to the status of work of some sort. If you have ONE table that has EVERYTHING, from the beginning of time, you have both current and historical data that can be a valuable asset for determing what has to be done and what has been done, when.

Been there; done that, and I've seen both the laughter and tears.

Skip,

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

 

[Predator98]

Knowledgeable AND a prophet!

Well, MS Query is out. Management's desire is to have it as I originally stated. I do understand attempting to keep the historical data but management wants the "completed" to be separated. And, you are correct in that this spreadsheet data is related to the status of work and the production of each user who has entry rights. I do appreciate your input though. I'm going to start hitting the manuals to see if I can figure this one out.

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

 

[SkipVought]

Well, you have and example of code running in a worksheet_change event as an example.

All you need, is to record copying the row and pasting in the Complete sheet.

Post back with your modified code where you need help.

Skip,

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

 

[Predator98]

Okay, here's my attempt but it is failing on the ActiveCell.EntireRow.Cut statement. Any suggestions or am I way off base on the entire block of code? The trigger should be the selection of "COMPLETED" and either cell BSxx or BTxx must have a value. Thanks in advance.

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

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

 

[SkipVought]

     If Range("BV" & Target.Row).Value = "COMPLETED" And (Range("BS" & Target.Row).Value <> "") Or (Range("BT" & Target.Row).Value <> "") Then

What this says is to cut the row when ANY change is made on a row AND...
[tt]
EITHER

column BV value for row is "COMPLETED" AND column BS value for row is ""

OR

column BT value for row is ""
[/tt]
Is that what you want?



Skip,

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

 

[SkipVought]

something like this...

    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
            Worksheets("Group Tracking Report").EntireRow.Copy
            
            Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
            
            Worksheets("Group Tracking Report").EntireRow.Delete Shift:=xlUp
        End If
    End If

Skip,

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

 

[Predator98]

Sorry I haven't responded sooner...been tied up with another task...aahhhh, a mainframe task! I appreciate the code. I inserted this into my code but it stops at statement:

Worksheets("Group Tracking Report").EntireRow.Copy

It pops up the following: "Run-time error '438': Object doesn't support this property or method."



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

 

[SkipVought]

oops. My mistake

With Worksheets("Group Tracking Report")
    .Cells(.cells.rows.count, 1).End(xlup).offset(1).EntireRow.Copy
            
     Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
            

    .Cells(.cells.rows.count, 1).End(xlup).offset(1).EntireRow.Delete 
End With

Skip,

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

 

[Predator98]

Thank you. I modified my code to match what you provided. The first thing I encountered though was that it attempted to write to the "Completed" sheet bu it gave me an error that the worksheet is protected (which I had set it to be). I have attempted two different scenarios with the same result. Scenario 1: I added code to uprotect the worksheet at the beginning of my code and a line to protect the worksheet at the end of my code. Scenario 2: Unprotected the worksheets. When I select "Completed" in the dropdown the code takes me to the "Completed" sheet and then back to the "In_Progress" sheet but the line is not copied/cut nor pasted. Also, the first available line in the "Completed" sheet is row 53 but the code attemptes to write the copied line to row 2001. Lengthy, yes, but I attempted to be as terse as possible to get all this in. Any ideas?

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

 

[SkipVought]

Also, the first available line in the "Completed" sheet is row 53 but the code attemptes to write the copied line to row 2001."

You have SOMETHING in A2001, maybe a SPACE.

"... but the line is not copied/cut nor pasted"

Crystal ball is cloudy today. Post your code in question.


Skip,

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

 

[Predator98]

Sorry to hear about your crystal ball! My code is below. I also have: Sheets("Completed Group Tracking").Unprotect following the Sub and: Sheets("Completed Group Tracking").Protect before the End-Sub.

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")
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Copy
Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
.Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Delete
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!

 

[SkipVought]

Have you stepped thru the code in order to observe what is happening?

If so, what did you observe?

Skip,

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

 

[Predator98]

What I just noticed was that it is copying the format but not the data in the cells.

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

 

[SkipVought]

COPY copies everything.

It is PasteSpecial that has options.

You have no other code running? No other events firing?

So you actually stepped thru, statement by statement and observed NOTHING being pasted?

Using a test, change the copy and pastespecial to SELECT...

           With Worksheets("Group Tracking Report")
              .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.[b]SELECT
   Sheets("Completed Group Tracking").ACTIVATE     [/b]      
              Sheets("Completed Group Tracking").Range("A65536").End(xlUp).Offset(1, 0).[b]SELECT
 ' [/b]            .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Delete
           End With

Skip,

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

 

[ck1999]

Just reading through the post but doesn't

 With Worksheets("Group Tracking Report")
              .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Copy

copy the empty row below the last row of data.
Should this not be replaced with the activerow.

This is why you are copying the format and no data

and also why it is not deleting the row from your table.

ck1999

 

[Predator98]

I tried Skip's suggestion and still no paste nor cut.

Skip, any comment to ck1999's suggestion?

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

 

[SkipVought]

Yup, that offset will definitely not copy ANY data.

Skip,

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