Automate data archiving processes, Excel VBA

Here’s an example of VBA code to automate the process of archiving data in Excel, with an explanation in English.

Objective:

This VBA code will copy rows from a source worksheet to an archive workbook based on a specific condition — in this case, if the date in column A is older than 30 days from the current date.

Example VBA Code:

Sub ArchiveData()
    Dim wsSource As Worksheet
    Dim wsArchive As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date
    Dim filePath As String
    Dim archiveWB As Workbook
    Dim rowRange As Range   
    ' Define the source worksheet (where the data to be archived is stored)
    Set wsSource = ThisWorkbook.Sheets("Data")   
    ' Define the current date
    currentDate = Date    
    ' Define the file path for the archive workbook
    filePath = "C:\Path\To\Your\Folder\Archive.xlsx"   
    ' Open the archive workbook
    Set archiveWB = Workbooks.Open(filePath)   
    ' Define the archive worksheet within the archive workbook
    Set wsArchive = archiveWB.Sheets("Archive")   
    ' Find the last row in the source worksheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row   
    ' Loop through the rows starting from row 2 (assuming row 1 is a header)
    For i = 2 To lastRow 
        ' Check if the date in column A is older than 30 days
        If wsSource.Cells(i, 1).Value < currentDate - 30 Then           
            ' Set the range for the row to be archived
            Set rowRange = wsSource.Rows(i)           
            ' Copy the row to the archive worksheet
            rowRange.Copy           
            ' Find the first empty row in the archive worksheet
            Dim lastRowArchive As Long
            lastRowArchive = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1           
            ' Paste the row into the archive worksheet
            wsArchive.Rows(lastRowArchive).PasteSpecial Paste:=xlPasteValues           
            ' Optionally, delete the row from the source worksheet after archiving
            rowRange.Delete           
            ' Adjust the loop to compensate for the deleted row
            i = i - 1
            lastRow = lastRow - 1
        End If       
    Next i
    ' Save and close the archive workbook
    archiveWB.Save
    archiveWB.Close  
    ' Inform the user that the archiving is complete
    MsgBox "Archiving completed successfully!", vbInformation
End Sub

Explanation of the Code:

1. Variable Definitions:
   • wsSource: Refers to the source worksheet (the one with the data to be archived).
   • wsArchive: Refers to the archive worksheet where the data will be copied to.
   • filePath: Specifies the path to the archive workbook (ensure you modify this with your actual file path).
   • currentDate: Stores the current date, used to compare against dates in the source worksheet.
2. Opening the Archive Workbook:
   • The archive workbook (Archive.xlsx) is opened using Workbooks.Open.
   • The wsArchive worksheet within the archive file is selected for storing the archived data.
3. Looping Through the Source Data:
   • The code loops through all the rows in the source worksheet starting from row 2 (assuming the first row contains headers).
   • It checks if the date in column A is older than 30 days. If the condition is met, the row is copied to the archive worksheet.
4. Copying and Deleting Data:
   • The row is copied and pasted into the first available row in the archive worksheet.
   • Optionally, after the data is archived, the row is deleted from the source worksheet.
   • To compensate for the row deletion, the loop index (i) and lastRow are adjusted.
5. Saving and Closing the Archive Workbook:
   • After the archiving process, the archive workbook is saved and closed.
6. User Notification:
   • A message box appears to notify the user that the archiving process is complete.

Customization of the Code:

• Date Criteria: You can adjust the condition currentDate – 30 to use a different date threshold.
• Specific Columns: If the data to be archived is in columns other than column A, modify wsSource.Cells(i, 1) to point to the correct column.
• Archive File Path: Make sure to update the filePath variable with the actual path to your archive workbook.

How to Use the Code:

1. Open the VBA editor (Alt + F11) in Excel.
2. Create a new module and paste the code into the module.
3. Run the ArchiveData macro to archive the old data based on your criteria.

This VBA script helps automate the process of archiving old data, making it easier to manage and keep your data organized without manual intervention.