Move columns in VBA

[k3vsmith]

I have excel exports that Financial Analyst send that I have a macro run against that does several different things. The issue I have is there is no standard yet set on number of columns and order (trying to get that worked out). There are only four columns I care about which I manually move to the end before running the macro. Sometimes the end is column I sometimes the end is column G. All depends on amount of columns the FA sends.

Is there any way to do this in VBA? Move these four columns to the end?

The four column names I care for:
RES CODE
OT
YYYYMM
HOURS/UNITS

 

[Luke M]

Give this a shot. Will place them in the order you list them in first array.

Sub MoveColumns()
Dim lastCol As Long
Dim i As Long
Dim colNames As Variant
Dim ws As Worksheet
Dim fCell As Range

'Which columns to move?
colNames = Array("RES CODE", "OT", "YYYYMM", "HOURS/UNITS")

'Which sheet to work on?
Set ws = ActiveSheet

Application.ScreenUpdating = False
With ws
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   
    'Find our columns
    For i = LBound(colNames) To UBound(colNames)
        Set fCell = .Range("1:1").Find(what:=colNames(i), MatchCase:=False, lookat:=xlWhole)
       
        'Error check if needed
        If fCell Is Nothing Then
            MsgBox "Couldn't find: " & colNames(i)
        Else
            'Move item to end
            fCell.EntireColumn.Cut
            .Cells(1, lastCol + 1).Insert Shift:=xlToRight
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

 

[k3vsmith]

Awesome, thanks. Ill test this out.

 

[k3vsmith]

@Luke M
This works great but changes the way I need to do another sub routine. My sub routine changes the name of column OT to Business. Then copies a formula down the Business column. See below for my code. I was doing this by specifying the column name as a variable based on a certain location. So before I was running macro I always had RES CODE in column k1 and Business in column l1. How can I search for these columns and put into variable so that this routine works?:

Const cnRescodeA = "k1"
Const cnBusinessA = "l1"

Sub sbCopyBusinessFormulaDownActuals()

     'change OT to Business
     
     Sheets("Actuals").Activate
             
     'copy the Business Formula down
     '=IF(MID(K2,4,2) = "PM", MID(K2,4,2), MID(K2,4,3))
   
     mod_utilities.sbPlaceValues stFormula:="=IF(MID(" & cnRescodeA & ",4,2) = ""PM"", MID(" & cnRescodeA & ",4,2), MID(" & cnRescodeA & ",4,3))", stAddy:=cnBusinessA

     Range(cnBusinessA).Select
     ActiveCell.FormulaR1C1 = "Business"
     
End Sub

 

[Luke M]

Like this?

Sub sbCopyBusinessFormulaDownActuals()
Dim fBusiness As Range
Dim fRes As Range
Dim cnRescodeA As String
Dim cnBusinessA As String

     'change OT to Business
   
     Sheets("Actuals").Activate
             
With ActiveSheet
    Set fBusiness = .Range("1:1").Find(what:="Business", lookat:=xlWhole, MatchCase:=False)
    Set fRes = .Range("1:1").Find(what:="RES CODE", lookat:=xlWhole, MatchCase:=False)
End With
cnRescodeA = fRes.Address(False, False)
cnbusinesssa = fBusiness.Address(False, False)

     'copy the Business Formula down
    '=IF(MID(K2,4,2) = "PM", MID(K2,4,2), MID(K2,4,3))
     mod_utilities.sbPlaceValues stFormula:="=IF(MID(" & cnRescodeA & ",4,2) = ""PM"", MID(" & cnRescodeA & ",4,2), MID(" & cnRescodeA & ",4,3))", stAddy:=cnBusinessA

     Range(cnBusinessA).Select
     ActiveCell.FormulaR1C1 = "Business"
     
End Sub

 

[k3vsmith]

Perfect, thank you!