VBA copy UsedRange of a column based on column header name

Question

I am trying to copy data from one worksheet to another based on the column-name. In the source worksheet, the data starts at A1. In the destination worksheet, the data should be pasted at row A11 and not A1. If I used EntireColumn.Copy I get an error about the source and destination copy area not being the same. I came across the UsedRange property but I am unbale to apply it to my scenario

    For Each columnName In allColumns
    'call a function to get the column to copy 
    If columnToCopy > 0 Then
        columnName.Offset(1, 0).EntireColumn.Copy Destination:=ws2.Cells(11, columnToCopy)
    End If
Next

In the above snippet, In dont want to use 'EntireColumn'. I only want the columns that have data. The variable columnName is for example 'Person ID'

What is the best way to do this?

Thanks.

Answer 1

This would be a typical approach:

    For Each ColumnName In allColumns
        If columnToCopy > 0 Then
            With ColumnName.Parent
                .Range(ColumnName.Offset(1, 0), .Cells(.Rows.Count, ColumnName.Column).End(xlUp)).Copy _
                              Destination:=ws2.Cells(11, columnToCopy)
            End With
        End If
    Next

Assumes allColumns is a collection of single-cell ranges/column headers.