Paste Data Below the Last Used Row not working

[Kasbres]

Hello,

I have a script that moves data from one sheet to another based on a criteria-- thanks to the help I got on this board . It moves what I need perfectly, BUT It is supposed to move below the last line of data -line 232 for this so it should go to line 233, but is going to line 5004. I can sort them into order but the next time data moves it goes to line 5005 not 234. I tried to clearing the lines, deleting the empty lines on the Data history tab, and it just keeps progressing after the last line in the 5000s. I am stumped.

Sub Move_Data()
'
' Move_Data Macro
' Move Data
'
' Keyboard Shortcut: Ctrl+Shift+M
'
ActiveSheet.Unprotect Password:="123"
Worksheets("DataHistory").Unprotect Password:="123"
Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
Application.ScreenUpdating = False
lastrow = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
lastrow2 = Worksheets("DataHistory").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 1
For r = lastrow To 2 Step -1
If Range("F" & r).Value = "Not Eligible" Or Range("I" & r).Value >= (1 / 1 / 80) Or Range("K" & r).Value >= (1 / 1 / 80) Or Range("L" & r).Value >= (1 / 1 / 80) Then
Rows(r).Cut Destination:=Worksheets("DataHistory").Range("a" & lastrow2 + 1)
Rows(r).Delete
lastrow2 = lastrow2 + 1
Else:
End If
Next r
Application.ScreenUpdating = True
Worksheets("DataHistory").Range("A1:N5000").Locked = True <<NOTE also tried Range(A:N)>>
Worksheets("DataHistory").Protect Password:="123"
ActiveSheet.Protect Password:="123"
End Sub

 

[MARK858]

What happens if you change

lastrow = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
 lastrow2 = Worksheets("DataHistory").UsedRange.Rows.Count

to

lastrow = ActiveWorkbook.ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
 lastrow2 = Worksheets("DataHistory").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

 

[Kasbres]

THANK YOU.. It works, it puts it two lines down, ie last line is 234 and it puts it on 237. but I can live with two blank lines. It doesn't "grow" either, it is always 2 blank rows.

On a side note do you know why the keyboard short cut no longer works. I can only get it to run from the toolbar? Worse case now that I know what to fix I can redo the whole script.

Thank you again.

 

[MARK858]

2nd problem first try...

Press Alt + F8
Select the macro
press options
put in the shortcut.

As the first problem what does the below give you?

Sub xxxx()
Dim x As Range
Set x = Cells(Worksheets("DataHistory").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row, Rows(Worksheets("DataHistory").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row).Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column)
MsgBox x.Value2 & vbLf & Len(x.Value) & vbLf & x.Address
End Sub

 

[MARK858]

Also why aren't you putting lastrow2 in the loop and removing lastrow2 = lastrow2 + 1?

 

[Kasbres]

I must have done something wrong, It popped up a window with the name of the class 44$N617. I assume the name of the class is pulling from column N where the class name is on each tab. It doesn't move anything? Was I supposed to insert it somewhere within the code...

I am now totally confused.

 

[MARK858]

It wasn't to move anything, it was because you are stating that the code is posting 2 rows down from the last row to find out exactly where the last row actually was.

So is this cell with this "class 44$N617" 2 rows below where you were hoping the code would take you?

 

[Kasbres]

As to Also why aren't you putting lastrow2 in the loop and removing lastrow2 = lastrow2 + 1? I really do not know.

I will be honest I am horrible with VBA scripts- I was asked to help out as the only person in our office that new anything at all about Excel. I pieced this together this from other scripts I found on this board and others over a year ago and I really do not remember how exactly I managed to get it to work. This was the 4th script I have ever done and I don't understand most of what I did. It was a lot of trial and error, I had over 52 versions at one point. Once I got it to work--or appeared to work, I was afraid to mess it up. So I REALLY appreciate your help on this but need more guidance on what you mean... Sorry

Thank you again!.

 

[MARK858]

What happens if you change

lastrow2 = Worksheets("DataHistory").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

to

lastrow2 = Worksheets("DataHistory").Columns("A:M").Find("*", , xlValues, , xlByRows, xlPrevious).Row