2

This site has helped me immensely with VBA for a while now, so thanks for that! But I just can't seem to get this code to work and I've look at so many examples. What's happening is that I'm archiving data on another sheet once the current date is 4 days ahead of the due date. Everything works like it should, but every time the macro executes, the data on sheet2 is erased and copied over. I need my code to find the last row on sheet2 and copy the data from sheet1 to sheet2 so all the data is there. Thanks!

Sub archive()

Dim LastRow As Long
Dim i As Long
LastRow = Range("M" & Rows.Count).End(xlUp).Row

For i = 3 To LastRow

If Worksheets("Sheet1").Range("M" & i) - Date <= -4 And Worksheets("Sheet1").Range("N" & i).Value = "DONE" Then
Sheet2.Select

Range("A" & i).EntireRow.Value = Sheet1.Range("M" & i).EntireRow.Value
Sheet1.Range("M" & i).EntireRow.Delete
End If

If Worksheets("Sheet1").Range("L" & i) = "" Then
Exit For
End If

Next i

End Sub
Community
  • 1
  • 1
Micah Lankford
  • 17
  • 6
  • 1
    [5 ways to find the last row](http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba) and [How to avoid using select](http://stackoverflow.com/q/10714251/4002530) are worth reading and will help you. – tospig Apr 29 '15 at 22:53
  • I looked at these awhile back. It makes sense to me...I'm just not sure how to implement it. When I put something like " LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row" inside the For Next statement I get an error. I've tried countless ways of putting something like that in it. So I just had to ask the question on here. – Micah Lankford Apr 29 '15 at 23:17
  • Did you define a `worksheet` object? e.g. `dim sht as Worksheet`, then `set sht = worksheets("Sheet1")`. You can then use `sht` in place of `If Worksheets("Sheet1")...` – tospig Apr 29 '15 at 23:23

1 Answers1

0

Here I've taken your code and changed it to use worksheet objects. I've not tested this on any data as you haven't provided any to use, but it gives you an idea of how to implement it.

Also, in your code you weren't finding the last row of Sheet2, you were putting the data in row i, which starts at 3.

You also need to watch out when you delete the row of data from sheet1, as this shifts the rest of the data up, so the next iteration of the loop may not find the next row of data/ skip a row of data.

Sub archive()

Dim LastRow As Long
Dim LastRowSht2 As Long
Dim i As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim rowCount As Long

Set sht1 = Worksheets("Sheet1")
Set sht2 = Worksheets("Sheet2")

LastRow = sht1.Range("M" & Rows.Count).End(xlUp).Row
rowCount = 3

For i = 3 To LastRow

    If sht1.Range("M" & rowCount) - Date <= -4 And sht1.Range("N" & rowCount).Value = "DONE" Then

        LastRowSht2 = sht2.Range("A" & Rows.Count).End(xlUp).Row + 1  '+1 so it doesn't overwrite the last row
        sht2.Range("A" & LastRowSht2).EntireRow.Value = sht1.Range("M" & rowCount).EntireRow.Value

        sht1.Range("M" & rowCount).EntireRow.Delete
    Else
        rowCount = rowCount + 1
    End If

    If sht1.Range("L" & rowCount) = "" Then
        Exit For
    End If

Next i

' clean up
set sht1 = nothing
set sht2 = nothing 

End Sub
tospig
  • 7,762
  • 14
  • 40
  • 79
  • I'm sorry I can't provide any real data! It's for my company. I can tell you that column N is text, M is a due date, and L is text. The spreadsheet is setup so that data begins on row 3 on each sheet. I need to clear the contents of the copied row and also move all the data up. It will never go past row 3 though so I think it should be okay if I delete it ya? I'm gonna play with this and get back to you. Thank you for being so helpful! – Micah Lankford Apr 29 '15 at 23:55
  • Also, there will be lots of data left so I don't need to clear the sheet. Just move the old data over. – Micah Lankford Apr 30 '15 at 00:04
  • That's fine; just be sure to debug the code and make sure the loop is still working and selecting the correct row after the delete. – tospig Apr 30 '15 at 00:05
  • Okay so that works. Now it's only finding two rows and ending the macro even though other rows met the requirements. Is that what you were talking about? Cause now I think I understand what you are saying. Is it possible to move everything after the For Next statement? Or direct it back to the 3rd row on sheet one? – Micah Lankford Apr 30 '15 at 00:15
  • I've updated the code to include a `rowCount` variable that starts at 3, but will not increment if a row is deleted. That way it will re-check row 3 if the previous iteration of the loop deleted that row. – tospig Apr 30 '15 at 00:24
  • Awesome! Definitely learned a lot from you. I just need to iron out some kinks that I'm determined to do by myself, and I'll be good to go. Thanks again. Thanks for helping out this novice. – Micah Lankford Apr 30 '15 at 01:34