Archive Excel Data Using VBA

How to archive Excel data using VBA or Visual Basic for Applications

First watch the training video and then study the details of the automated process:

You can download the sample file by clicking on the Excel icon:

You can view the training video also on YouTube.

A team leader in a Business Process Outsourcing company has to manage many projects with his team. Now when the project is delivered satisfactorily to his customers he wishes to archive the Excel data from his projects worksheet to his archived or delivered worksheet automatically.

Under the status header he would like to enter ‘delivered’. As soon as he does this he wishes that the data be transferred to another worksheet. There are two interesting approaches to solve this problem. One is to use the change property of the target cell in the relevant worksheet. As soon as the data in the target cell becomes ‘delivered’ or ‘DELIVERED’  i. e. it changes, we transfer the data to the ‘delivered’ worksheet. Also we remove or delete the row of data from the projects sheet. This effectively means that the rows of data is slowly being removed from the ‘projects’ worksheet as the projects get completed and the rows of data in the ‘delivered’ sheet keeps on increasing because more delivered projects data is being added.

So let’s study the complete code for the events that happen when there is a change in the worksheet data i. e. when the string or text ‘delivered’ is added uner the header ‘status’:

Private Sub Worksheet_Change(ByVal Target As Range)

‘First we ensure that the Excel’s action messages or events are displayed so that the process doesn’t need the user intervention and the complete process of archiving the data is automated

Application.EnableEvents = False

‘We check for a condition. We check whether the column 3 or C has the text ‘delivered’ or ‘DELIVERED’

If Target.Column = 3 And UCase(Target) = “DELIVERED” Then

‘If the text ‘delivered’ is found we copy the entire row, find the last-row plus one in the delivered worksheet and paste the entire row of data there

Cells(Target.Row, Target.Column).EntireRow.Copy Destination:=Sheets(“delivered”).Range(“A” & Rows.Count).End(xlUp).Offset(1)

‘We also delete the row from the projects sheet after archiving the data in sheet ‘delivered’

Rows(Target.Row & “:” & Target.Row).Delete
End If

‘Now we activate the Excel’s events features to warn us

Application.EnableEvents = True
End Sub

The second approach is to create a subroutine or macro in such a way that we run the macro once we have decided which projects are over and then archive this data. This approach is more sensible and less error prone. We can study the complete macro  VBA code:

Sub archive()
‘We define the variables and let Excel decide the data type
Dim i, lastrow
Dim mytext As String
‘We find the last row used in the sheet projects
lastrow = Sheets(“projects”).Range(“A” & Rows.Count).End(xlUp).Row
‘We loop through the data in the sheet projects to discover the ‘delivered’ projects
For i = 2 To lastrow
mytext = Sheets(“projects”).Cells(i, “C”).Text
‘The InStr function can extract the text or string ‘delivered’ also from a sentence
If InStr(mytext, “delivered”) Then
‘Once the string ‘delivered’ is found, we copy the relevant row and paste it into the delivered sheet
‘The process of pasting also involves the automatic discovery of the next blank row
Sheets(“projects”).Cells(i, “A”).EntireRow.Copy Destination:=Sheets(“delivered”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
‘Finally we delete the entire transferred row from the projects worksheet
Sheets(“projects”).Cells(i, “A”).EntireRow.Delete
End If
Next i

End Sub

You can also attach the archive macro to a command button to run the macro fast and easily. The image and the code below demonstrates the idea:

attach archive macro to command button
Attach Archive Macro to Command button

Private Sub CommandButton1_Click()
End Sub