Change column name in a table

[craig2525]

After much searching with no answers, I need some help. Not even sure this can be done. I need to change the Column names in a table based on cells. I would like to change Column 1 to match what is in cell A2, Column 3 to match cell C2 and so on. Is this possible?

 

[My Aswer Is This]

Are you sure we are dealing with a "TABLE"?
And why are we going with column A then Column 3?
And what does:
Your quote "and so on" mean"
There are several thousand columns on a sheet.

And if we are actually working with a "Table"
We need sheet name and Table name

 

[Trevor G]

What if you scroll down slight does the table headings replace the column letters? You could also look at using freeze panes or split screens if you want to keep the dates in row two visible.

You cant change the column letters to your own text headings.

 

[RoryA]

You'd need code to do that (a simple loop will do), but I'm not sure why you want to? Your data layout doesn't really suit a table given that your even numbered columns all relate to the same thing (PM Hours), so unless you need to filter, I'd just hide the real column headers. Anyway, sample code would be:

Sub renameCols()
   Dim ws As Worksheet
   Set ws = ActiveSheet
   Dim tb As ListObject
   Set tb = ws.ListObjects(1)
   
   Dim col As Long
   For col = 1 To tb.ListColumns.Count Step 2
      tb.ListColumns(col).Name = ws.Cells(2, col).Value
   Next col
End Sub

 

[craig2525]

It is a table. I need to make the Column name match the cell (which will change weekly). "And so on" means each day of the week I have listed (Friday to Friday). Table name is Table1.

 

[craig2525]

 

[craig2525]

The date in green is in cell AL5. Sheet name is Sheet1.

 

[Peter_SSs]

Sheet name is Sheet1.

Assuming that is the Tab name of the worksheet and this is the only formal table on that worksheet, give this modification a try with a copy of your workbook.

Sub Rename_Cols()
  Dim tb As ListObject
  Dim col As Long
  
  Set tb = Sheets("Sheet1").ListObjects(1)
  For col = 1 To tb.ListColumns.Count Step 2
    With tb.ListColumns(col)
      .Name = Format(.Range.Cells(-1, 1).Value, "m/d/yyyy")
    End With
  Next col
End Sub

 

[craig2525]

Works like a charm. Thank you Peter_SSs!!

​

 

[Peter_SSs]

You're welcome. (It is largely Rory's suggestion from post 4 altered to suit the changed table location from your initial picture )