modify VBA code to include dynamic columns instead of static

gms4b

In my opinion, an embedded button on each worksheet is not a good idea!

You can run the code from the https://www.excel-easy.com/vba/examples/add-a-macro-to-the-toolbar.html or from https://www.youtube.com/watch?v=dmdolFcS-fI tab.

Or you can place the buttons in one worksheet as follows:

Also, grouping all the process code in one Sub is also not a good idea!

You can instead separate them in three Subs so that each Sub handle one part of the automation.

Then move all Subs in a new generic module.

Then create a sub called (Main) and call the other subs in it.

This makes the code more readable and maintainable.

This is the full Module1 code including all Subs.

Sub Main()
    Call FilterData
    Call MoveToUndilutedPLUS
    Call MoveToDilutedPLUS
End Sub

Sub FilterData()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Worksheets("Raw Data").Activate
    
    Dim a As Long
    a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim headerColumn1 As Long
        headerColumn1 = Cells.Find(What:="Sample Type", After:=Range("A1"), LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Column
    Dim headerColumn2 As Long
        headerColumn2 = Cells.Find(What:="Dilution Factor", After:=Range("A1"), LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Column
                   
    For i = 2 To a
        If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value = "1") Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Undiluted").Activate
            Dim b As Long
            b = Worksheets("Undiluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Undiluted").Cells(b + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If

        If ((Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Unknown" Or Worksheets("Raw Data").Cells(i, headerColumn1).Value = "Quality Control") And Worksheets("Raw Data").Cells(i, headerColumn2).Value > 1) Then
            Worksheets("Raw Data").Rows(i).Copy
            Worksheets("Diluted").Activate
            Dim c As Long
            c = Worksheets("Diluted").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Diluted").Cells(c + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Raw Data").Activate
        End If
        
    Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Undiluted").Range("A1:CZ1")
    Sheets("Raw Data").Range("A1:CZ1").Copy Destination:=Sheets("Diluted").Range("A1:CZ1")
    
    Next
    
    Worksheets("Raw Data").Activate
    Worksheets("Raw Data").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

Sub MoveToUndilutedPLUS()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    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
    Sheets("UndilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName
    
    Sheets("UndilutedPLUS").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

Sub MoveToDilutedPLUS()
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Dim i As Integer
    i = 0
    
    Sheets("Diluted").Activate
    Dim columnNamesArray() As Variant
    columnNamesArray = Array("Sample Type", "Rack Type")
    
    Dim columnName As Variant
    For Each columnName In columnNamesArray
    Sheets("Diluted").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("DilutedPLUS").Activate
    Sheets("DilutedPLUS").Range("A1").Select
    ActiveCell.Offset(0, i).Select
    ActiveSheet.Paste
    i = i + 1
    Next columnName
    
    Sheets("DilutedPLUS").Range("A1").Select
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

This also makes the code reusable as the embedded buttons don't duplicate the same code, it just reuses it or recalls it.

This is the embedded buttons code behind the Run sheet:

Please find all that in the attached worksheet.

Regards