Find column by header name Copy UsedRange of column to last column + 1

[Tim Hanson]

Hello

I am tring to find a column by the header name then copy the UsedRange of the column to the last column + 1

I am not getting this part right I think ".UsedRange.Range(c.Address).Copy"

Thank you for your consideration.

Sub CopyColToLastCol()
    Dim lastHeaderCol As Long
    Dim lRow As Long
    Dim rNameHeader As Range
   
    Set ws = ActiveWorkbook.Sheets("LI - names fixed")
       
    With ws.UsedRange
    'determine last filled cell in first row
      lastHeaderCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                lRow = .Range("A" & .Rows.Count).End(xlUp).Row
               
    Set c = .Find("email address", LookIn:=xlValues)
   
    If Not c Is Nothing Then
        .UsedRange.Range(c.Address).Copy _
                            Destination:=.Range(Cells(1, lastHeaderCol + 1), Cells(lRow, lastHeaderCol + 1))
    End If
End With
       
End Sub

 

[Tim Hanson]

I got it though it seems a little bit cumbersome, if anyone sees a simpler way I would appreciate it

Thanks

Sub CopyColToLastCol()
    Dim lastHeaderCol As Long
    Dim lRow As Long
    Dim colLetter
   
    Set ws = ThisWorkbook.Sheets("LI - names fixed")
   
    'determine last filled cell in first row
    lastHeaderCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
                   
    With ws
        Set c = .UsedRange.Find("email address", LookIn:=xlValues)
    colLetter = Mid(c.Address, 2, 1)
   
    If Not c Is Nothing Then
    .Range(c.Address & ":" & colLetter & lRow).Copy _
                                    Destination:=.Range(Cells(1, lastHeaderCol + 1), Cells(lRow, lastHeaderCol + 1))
    End If
End With

End Sub