VB help

Please can you help with the attached file.

I would like to copy & paste special the values on sheet 1 into sheet 3

Sheet 1 Range B9:S108

Sheet 3 starting with B9 and onward

However I only wish to export lines which have an end date in column N, the lines which do not will then shift up the page towards B9 etc

The idea is to do this once a week so that in sheet 3 old lines are added and used as an archive
help---11-09-2012.xls
Kiwi-123Asked:
Who is Participating?
 
tdlewisCommented:
I think you want your button to do this:
Private Sub CommandButton3_Click()
Dim i As Long
Dim row As Range
    
    j = Sheets("Archive").Range("B" & ActiveSheet.Rows.Count).End(xlUp).row + 1
    For i = 9 To ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row
        Set row = ActiveSheet.Range("A" & i)
        If row.Cells(1, 14).Value <> "" Then
            Sheets("Archive").Range("B" & j & ":S" & j).Value = ActiveSheet.Range("B" & i & ":S" & i).Value
            j = j + 1
        End If
    Next i
    ' Delete the archived rows
    For i = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row To 9 Step -1
        Set row = ActiveSheet.Range("A" & i)
        If row.Cells(1, 14).Value <> "" Then
            row.Rows(1).EntireRow.Delete
        End If
    Next i
End Sub

Open in new window

0
 
Kiwi-123Author Commented:
Perfect! Many thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.