Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

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
1 Solution
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
        End If
    Next i
End Sub

Open in new window

Kiwi-123Author Commented:
Perfect! Many thanks for all your help.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now