[SOLVED] Apply VBA macro to column and not only selected cell

Apply VBA macro to column and not only selected cell

I have created a function that makes the selected cell to a hyperlink to a sheet in my excel with the same name. That is, if my cell says "Test", the text will become a link to the sheet with the name "Test", if this sheet exists. I would like the function to be executed for all the cells in column A from A7 down to the last cell with a value instead of just the one cell. I can't figure out how to change my code in order to do so. Please help! My code is the following:

Sub CreateHyperlinkToSheet()
    Dim selectedCell As Range
    Set selectedCell = Selection

    selectedCell.Hyperlinks.Add Anchor:=selectedCell, Address:="#'" & selectedCell.Value & "'!A1", TextToDisplay:="" & selectedCell.Value & ""

End Sub

Function SheetExists(sheetName As String) As Boolean
    On Error Resume Next
    SheetExists = (Len(Sheets(sheetName).Name) > 0)
    On Error GoTo 0
End Function

I can only make the function work for the selected cell and not for many cells at the same time.

Solution

If you're ok with selecting the cells before running the code, consider the following where you use a For Each-loop:

Sub CreateHyperlinkToSheet()
    Dim selectedCell As Range
    For Each selectedCell in Selection

        selectedCell.Hyperlinks.Add Anchor:=selectedCell, Address:="#'" & selectedCell.Value & "'!A1", TextToDisplay:="" & selectedCell.Value & ""

    Next

End Sub