Export Columns to New Worksheet

If the columns in question have a specific value at the top, a header, then you can spot the location of the column(s) at any given moment by searching for that specific value in the row the headers are found in, typically row1.

Sub CreateExportSheet()
Dim ColCopy As Long
Dim Dest    As Worksheet
    
    If Evaluate("ISREF('Export Sheet'!A1)") Then
        MsgBox "The sheet Export Sheet already exists"
        Exit Sub
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Export Sheet"
    End If
    
Application.ScreenUpdating = False
Set Dest = Sheets("Export Sheet")

On Error Resume Next
With Sheets("Sheet1")
    ColCopy = .Rows(1).Find("First Name", After:=.Cells(1, .Columns.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    
    If ColCopy > 0 Then
        .Columns(ColCopy).Copy Dest.Range("A1")
        ColCopy = 0
    End If
    
    ColCopy = .Rows(1).Find("Last Name", After:=.Cells(1, .Columns.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    
    If ColCopy > 0 Then
        .Columns(ColCopy).Copy Dest.Range("B1")
        ColCopy = 0
    End If
    
    ColCopy = .Rows(1).Find("Telephone", After:=.Cells(1, .Columns.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    
    If ColCopy > 0 Then
        .Columns(ColCopy).Copy Dest.Range("C1")
        ColCopy = 0
    End If

'...etc
End Sub

It's lengthy, but robust...

This is robust, too, you need only expand the two arrays. The first array is the value you're searching for, the second array is the target address for when the value is found.

Sub CreateExportSheet()
Dim Dest    As Worksheet
Dim ColCopy As Long
Dim Val     As Long
Dim MyStrs  As Variant
Dim MyTrgt  As Variant
    
    If Evaluate("ISREF('Export Sheet'!A1)") Then
        MsgBox "The sheet Export Sheet already exists"
        Exit Sub
    Else
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Export Sheet"
    End If
    
Application.ScreenUpdating = False
Set Dest = Sheets("Export Sheet")

MyStrs = Array("First Name", "Last Name", "Address", "City")
MyTrgt = Array("A1", "B1", "C1", "D1")

On Error Resume Next
With Sheets("Sheet1")

    For Val = LBound(MyStrs) To UBound(MyStrs)
        ColCopy = .Rows(1).Find(MyStrs(Val), After:=.Cells(1, .Columns.Count), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    
        If ColCopy > 0 Then
            .Columns(ColCopy).Copy Dest.Range(MyTrgt(Val))
            ColCopy = 0
        End If
    Next Val

End With

Set Dest = Nothing
End Sub

So you change the .Row(1) to .Row(5), yes?

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

First suggestion is always post a sample file where we can simply see the same things at the same time.
(Go Advanced > Paperclip).

Maybe this:

        ColCopy = .Rows(5).Find(MyStrs(Val), After:=.Cells(5, .Columns.Count), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column

Maybe add this:

With Sheets("Sheet1")

    For Val = LBound(MyStrs) To UBound(MyStrs)
        ColCopy = .Rows(5).Find(MyStrs(Val), After:=.Cells(5, .Columns.Count), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
    
        If ColCopy > 0 Then
            .Columns(ColCopy).Copy Dest.Range(MyTrgt(Val))
            ColCopy = 0
        End If
    Next Val

    .Range("AZ:FC").Copy Dest.Range("FA1")
End With

Columns in which sheet?
Which columns?