modify VBA code to include dynamic columns instead of static

gms4b

Hi,

Please try this code to move the needed columns from Undiluted to UndilutedPLUS.

Sub test2()

Application.ScreenUpdating = False
    
    Dim i As Integer
    i = 0
    
    Sheets("Undiluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Analyte Peak Name", "Sample Type", "Rack Type", "Vial Position")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    
    Sheets("Undiluted").Activate
    
    Dim columnNumber As Integer
    columnNumber = Cells.Find(What:=columnName, After:=Range("A1"), LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).Column
                   
    Cells(1, columnNumber).EntireColumn.Copy
    
    Sheets("UndilutedPLUS").Activate
    Range("A1").Activate
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName

Range("A1").Activate
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Please note that you have to define the needed columns you want to move in the code.

You can insert them into the columnNamesArray.

Hope that helps