Macro to Add Column Name instead of Headers

[AriffChowdhury]

I have a macro that does the following;

Sub CreateHeaders()

    Dim wsData As Worksheet
    Dim wsHeaders As Worksheet
    Dim headerRange As Range
    Dim header As Range
    Dim i As Long: i = 0
    Dim anchor As Range
    Dim subAddr As String


    Set wsData = Worksheets("Sheet1")
    Set wsHeaders = Worksheets("Sheet2")
    Set headerRange = wsData.Range("A1", wsData.Range("A1").End(xlToRight))
    Set anchor = wsHeaders.Range("B5")  '## begin inserting the hyperlinks at B5


    For Each header In headerRange  '## iterate over each cell in the header row
        subAddr = "'" & wsData.Name & "'!" & header.Address
        With wsHeaders
            .Hyperlinks.Add anchor:=anchor, Address:="", SubAddress:= _
                 subAddr, TextToDisplay:=header.Value
        End With
        i = i + 1
        Set anchor = anchor.Offset(1, 0) '## increment the location of the next hyperlink, to the next row
    Next
End Sub

It copies the header name but i want that to copy COLUMN NAME Like "COLUMN A","COLULMN B","COLUMN C" and so on...

Thank you!

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

I made some changes (in blue)

See if this is what you want

Sub CreateHeadersNew()
    Dim wsData As Worksheet
    Dim wsHeaders As Worksheet
    Dim headerRange As Range
    Dim header As Range
    Dim i As Long: i = 0
    Dim anchor As Range
    Dim subAddr As String
    [COLOR=#0000ff]Dim colLetter As String[/COLOR]
   
    Set wsData = Worksheets("Sheet1")
    Set wsHeaders = Worksheets("Sheet2")
    Set headerRange = wsData.Range("A1", wsData.Range("A1").End(xlToRight))
    Set anchor = wsHeaders.Range("B5")  '## begin inserting the hyperlinks at B5

    For Each header In headerRange  '## iterate over each cell in the header row
        subAddr = "'" & wsData.Name & "'!" & header.Address
        
        [COLOR=#0000ff]With wsHeaders.Columns(header.Column)
            colLetter = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
        End With
[/COLOR]     
        With wsHeaders
            .Hyperlinks.Add anchor:=anchor, Address:="", SubAddress:= _
                 subAddr, TextToDisplay:=[COLOR=#0000cd]"Column " & colLetter[/COLOR]
        End With
        i = i + 1
        Set anchor = anchor.Offset(1, 0) '## increment the location of the next hyperlink, to the next row
    Next
End Sub

M.

 

[AriffChowdhury]

Dear Marcelo Branco
This is exactly what I needed for..
Thank you so much for your reply!

And a little question for my own knowledge......I have another question should I start new thread for this ?

 

[Marcelo Branco]

Dear Marcelo Branco
This is exactly what I needed for..
Thank you so much for your reply!

And a little question for my own knowledge......I have another question should I start new thread for this ?

You are welcome and thanks for the feedback.

About your question:
If the new question is not related with the macro above i suggest a new thread.

M.