June 4, 2014

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()
archive
End Sub

6 thoughts on “Archive Excel Data Using VBA

  1. Dear Sir i’m ramesh I have questions but how can i communicate with you????

  2. Dear Sir.

    I tried the following code from yours site and it not worked. I running Swedish Excel 2010.
    Do you have an sample for download of the file you are using in the video?

    —————————
    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
    —————————
    Many thanks to you for sharing yours knowledge 🙂

  3. Dear sir,
    I am work in private company, where i maintain large data sheet where my work is found error of other party, they have several column name , several header , i just want to take specific header with specific data through individuals criteria just like id name wise search but not take entire row only select row i want to take and copy another sheet, could you help me to do this work through vba excel

    Thanks
    Mishu

  4. Thank you for your video titled: Archive Excel Data Using VBA. I found the information very useful with the project I am currently working on. The ability to label a row in one worksheet and copy the entire row to another work sheet it’s very useful. I would like to refine the process further by being able to just select certain cells in that labeled row on one work sheet and copy and paste them in a specific order/sequence to a single row on a second worksheet which is my “archive”.

    The application is a workbook with two worksheets. The “source” worksheet lists rows of stocks each stock (row) has 17 columns of data for that particular stock. When a stock is sold, 2 more columns are “filled in” (sell date [column #18] and sale price [column #19]) and the final column (#20) contains the archiving trigger word “sold”.

    Following your 1st method, I have been able to archive all the data (20 columns) to the archive worksheet. What I’d like to figure out is how to copy specific cells in the target row (specifically columns 1, 2, 7, 18, 9, 19, 4, & 3 which are columns A, B, G, R, I, S, D, & C) and paste them in the above order on the archive worksheet. Do you have any suggestions on how I can get started on creating the VBA code to archive specific cells in a target row? Thank you again for the video and for any help you can offer.

Comments are closed.