Not sure why the rows are overwritten???

[ohrammer]

I have found the following code on here and it does nearly what i want but i need to make some mods but have no idea how????
bascally this code looks at the text in Cell 11 and if it says "complete" moves the entire row to another work sheet labelled "completed".
My problem is that it seems to place the cut row on any row in the completed sheet not the next avalible line???
Additionally i would like to add a date to the rows that are moved ???

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Count > 1 Then Exit Sub
If Target.Column = 11 And LCase(Target.Value) = "complete" Then
Application.EnableEvents = False
With Sheets("completed")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
Target.EntireRow.Delete
Application.CutCopyMode = False
Application.EnableEvents = True
End With
End If
End Sub

[Miroslav R.]

Hi,
just tested and it does what it should = copy source to next available line (my 'completed' sheet was clear).
for example: When you want to place a date before the coppied line, do not copy entire row, but only a range and paste to completed.range("B" & LR+1) and you can enter a date to completed.range("A" & LR+1) after that.

[jaslake]

Hi orammer

Welcome to the Forum!!

Please add Code Tags to your Code...

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

With regard to your issue...you may have extraneous Data below your expected last row. Delete all the Cells below your expected last row and try again.

[ohrammer]

I have tried but failed to find the problem???
Every time i complete another row, it moves to the second tab but overwrites what was there so its a bit of a mystery.
Im trying to build a history of all the completed lines
Any other ideas??

[TMS]

As already requested, please add Code Tags to your post.

Please post a sample workbook with ant sensitive data replaced by boiler plate text so we can see the typical content and structure of the data.

Your code may work well given the right data layout ... as Miroslav R. has demonstrated.

Regards, TMS

[ohrammer]

My appologies i hope i have the tag in the right place
I will upload a copy of the spreadsheet

Please Login or Register  to view this content.

[JOHN H. DAVIS]

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

Please Login or Register  to view this content.

... and appear like this when posted:

[Select Code] copy to clipboard

your code here ...
and here ...
and here

You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.

[ohrammer]

meeting minutes test.xlsm

my spreadsheet when items get completed in colum D the row should move to the completed tab

[TMS]

Please Login or Register  to view this content.

You were using column A to determine the next available row. But there was nothing in column A, hence it overwrites the data already there.

Regards, TMS

[TMS]

PS: moral of the story ... if you post a sample workbook at the outset, the problem will get fixed quicker.

Oh, and you're not actually using the LR variable, hence I have commented it out here.

Regards, TMS

[ohrammer]

Thanks for your help

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.