Removing and ordering columns in the exported Excel file

Sometimes you need to perform some processing on the Excel file and you may need columns to be in specific order, or you may want it to contain only specific columns, without any extra. For this scenario, you can use the following VBA script to clean-up the Excel export of WorklogPRO.

You can change name and order of column on the line 5.

Sub MoveAndDeleteColumns()

    ' Define the array of column names you want to keep
    Dim columnNames() As Variant
    columnNames = Array("Project Key", "Issue Key", "Work Type", "Billable") 'replace with your column names
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Worklog") 'change Sheet1 to the name of your sheet
    
    Application.ScreenUpdating = False
    
    ' Move specified columns to the beginning of the sheet
    Dim colIndex As Integer
    Dim foundCol As Range
    colIndex = 1
    For Each colName In columnNames
        Set foundCol = ws.Rows(1).Find(What:=colName, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCol Is Nothing Then
            If foundCol.Column <> colIndex Then
                foundCol.EntireColumn.Cut
                ws.Columns(colIndex).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
            colIndex = colIndex + 1
        End If
    Next colName
    
    ' Delete remaining columns
    Dim totalColumns As Integer
    totalColumns = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    If totalColumns > UBound(columnNames) Then
        ws.Range(ws.Cells(1, colIndex), ws.Cells(1, totalColumns)).EntireColumn.Delete
    End If

    Application.ScreenUpdating = True

End Sub