VBA Help to find a column based on header value and cupy it to an other worksheet

Question

I have a simple program that I may use to copy the necessary table columns to another worksheet. My issue is that every time I try to edit it such that the header isn't copied and pasted, an error is returned. Here's my code:

Sub CopyColumns()

    Dim wsSource, wsResult As Worksheet
    Dim Name, UniqueId, OperatingStatus As Long
       
    Set wsSource = ThisWorkbook.Sheets("Source")
    Set wsResult = ThisWorkbook.Sheets("Result")
    
    Name = wsSource.Rows(1).Find("#BASEDATA#name").Column
    UniqueId = wsSource.Rows(1).Find("#BASEDATA#uniqueId").Column
    OperatingStatus = wsSource.Rows(1).Find("#BASEDATA#operatingStatus").Column
    
    If Name <> 0 Then
        wsSource.Columns(Name).Copy Destination:=wsResult.Columns(3)
    End If
    If UniqueId <> 0 Then
        wsSource.Columns(UniqueId).Copy Destination:=wsResult.Columns(4)
    End If
    If OperatingStatus <> 0 Then
        wsSource.Columns(OperatingStatus).Copy Destination:=wsResult.Columns(1)
    End If
    
End Sub

Any ideas on how to solve it? I tried to copy it like this using offset:

If targetColName <> 0 Then
wsSource.Columns(targetColName).Offset(1, 0).Resize(wsSource.Rows.Count - 1).Copy _ Destination:=wsResult.Columns(3).Offset(1, 0)

It gives Error: Application-defined or object-defined error

Answer 1

You can break out the "copy column if found" into a separate sub:

Sub CopyColumns()

    Dim wsSource, wsResult As Worksheet
    
    Set wsSource = ThisWorkbook.Sheets("Source")
    Set wsResult = ThisWorkbook.Sheets("Result")
    
    CopyIfExists wsSource.Rows(1), "#BASEDATA#name", wsResult, 3
    CopyIfExists wsSource.Rows(1), "#BASEDATA#uniqueId", wsResult, 4
    CopyIfExists wsSource.Rows(1), "#BASEDATA#operatingStatus", wsResult, 1
    
End Sub

'Look for `colName` in `headerRow`, and if found copy the whole
'  column to column `destColNum` on `destSheet`
Sub CopyIfExists(headerRow As Range, colName As String, destSheet As Worksheet, destColNum As Long)
    Dim f As Range
    Set f = headerRow.Find(what:=colName, lookat:=xlWhole) 'or xlPart
    If Not f Is Nothing Then
        f.EntireColumn.Copy destSheet.Cells(1, destColNum)
    End If
End Sub