CODEWITHSUNDEEP
excelvba
Newbie
Newbie

Reputation: 81

VBA Updating Table Headers from a List

I have a Table containing a list of Names.

I would like to update a secondary table to have headers that correspond to this list of names (starting from column 2).

Example if my original List contained a,b,c. I would like my secondary table to have column headers of "blank",a,b,c

    With Range("Original Table[Names]")
        Range("Secondary Table").HeaderRowRange.Value = .Value
    End With

The above gives me errors, however, i can't seem to figure out how to solve this

Upvotes: 0

Views: 710

Answers (1)

BigBen
BigBen

Reputation: 50008

Use Transpose, and Resize.

With Range("Original Table[Names]")
    Range("Secondary Table").HeaderRowRange.Cells(2).Resize(,.Rows.Count).Value = Application.Transpose(.Value)
End With

Perhaps a personal preference, but I'd work with ListObjects here:

Dim origTable As ListObject
Set origTable = ThisWorkbook.Worksheets("insertname").ListObjects("Original Table")

Dim secTable As ListObject
Set secTable = ThisWorkbook.Worksheets("insertname").ListObjects("Secondary Table")

With origTable.ListColumns("Names").DataBodyRange
    secTable.HeaderRowRange.Cells(2).Resize(,.Rows.Count).Value = Application.Transpose(.Value)
End With

Also, note that a column header can't be blank in a table.

Upvotes: 3

Related Questions