VBA Change Entire Columns with specific header to Value instead of Formulas

[LNG2013]

Hello,

I need help with a bit of VBA code. I have columns AIFinal01-AIFinal20 throughout my sheet. Each column contains a formula going to the bottom. I need to change that formula to a value for all 20 columns. The columns are not in direct order and their are columns between them. Any help is always appreciated. Thank you.

 

[Joe4]

Are all the column headers in row 1?
Are there any column headers that begin with "AIFinal..." that you do NOT want included in this?

 

[Joe4]

Assuming my assumptions above are correct (header in row 1, change ALL columns starting with "AIFinal", then this code should do what you want:

Sub MyCopyValueMacro()

    Dim c As Long
    Dim lc As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last column in row 1 with headers
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns
    For c = 1 To lc
'       Check header
        If Left(Cells(1, c), 7) = "AIFinal" Then
'           Find last row in column with data
            lr = Cells(Rows.Count, c).End(xlUp).Row
'           Convert all formulas to values
            Range(Cells(2, c), Cells(lr, c)).Value = Range(Cells(2, c), Cells(lr, c)).Value
        End If
    Next c
    
    Application.ScreenUpdating = True
    
    MsgBox "Copy complete!"
    
End Sub

 

[LNG2013]

Thank you all of your assumptions are correct, and the code worked perfectly thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!