Hi guys,
I am trying to find a way to organize the columns of my sheet in ascending order, these columns are a mix of week/year and month/year and they are dynamic, changing every week. I could use the code below, but the problem is when the header changes the code stop working. Any suggestion?
| StockDes | PO/Line | Qty | 232020 | 242020 | 302020 | 312020 | 262020 | 252020 | 272020 | 1/08/2020 | 1/09/2020 | 1/10/2020 |
Code
Sub Reorder_Columns()
Dim ColumnOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
ColumnOrder = Array("Header 6", "Header 2", "Header 1", "Header 4", "Header 5", "Header 3")
counter = 1
Application.ScreenUpdating = False
For ndx = LBound(ColumnOrder) To UBound(ColumnOrder)
Set Found = Rows("1:1").Find(ColumnOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If
Next ndx
Application.ScreenUpdating = True
End Sub