CODEWITHSUNDEEP
vbahyperlinkcell
vinit
vinit

Reputation: 85

create hyperlink to new cell automatically

I have a code that creates a hyperlink to a new worksheet that is created, in another worksheet called 'Management', in cell 'A6'. How can I code it so that every time I create a new sheet, the hyperlink is created to different cell? For example, A7, A8, A9 etc for each new sheet is created.

Here is the code I have so far

Private Sub Button8_Click()

Dim newSheet As Worksheet
Dim newName As String

Do
newName = Application.InputBox("What do you want to name the new sheet?", Type:=2)
If newName = "False" Then Exit Sub: Rem cancel pressed

Set newSheet = ThisWorkbook.Sheets.Add

On Error Resume Next
    newSheet.Name = newName
    newName = Error
On Error GoTo 0

If newName <> vbNullString Then
    Application.DisplayAlerts = False
        newSheet.Delete
    Application.DisplayAlerts = True
    MsgBox newName
End If

Loop Until newName = vbNullString

ThisWorkbook.Worksheets("Version Checklist").Cells.Copy
newSheet.Paste

Dim targetSheet As Worksheet
Dim targetRange As Range
Dim linkedSheet As Worksheet
Dim linkRange As Range

'set variable to the sheet the hyperlink will link to
Set targetSheet = ThisWorkbook.Sheets(ActiveSheet.Name)

' specify the range on the summary sheet to link to
Set targetRange = targetSheet.Range("A1:Z100")

' set variable to sheet that will have the hyperlink
Set linkedSheet = ThisWorkbook.Sheets("Management")

' specify where on that sheet we'll create the hyperlink
Set linkRange = linkedSheet.Range("A6")

' create the hypperlink on the copied sheet pointing
' back to the summary sheet
linkedSheet.Hyperlinks.Add Anchor:=linkRange, Address:="", SubAddress:= _
    "'" & targetSheet.Name & "'!" & targetRange.Address, _
    TextToDisplay:=targetSheet.Name

End sub

Upvotes: 2

Views: 316

Answers (1)

Richard Lusch
Richard Lusch

Reputation: 1190

You could set a cell to be a permanent counter of the number of clicks you have gone through and use this value to determine the numerical portion of the cell address. Adding some code like this would help run the counter:

            Sub counter()

            Dim x As Range
            Set x = Range("A1")

                x = x + 1

            Range("A1").Value = x

            End Sub

Then you could write something like this for the hyperlink reference:

            Set linkRange = linkedSheet.Range("A" & x)

Every time you click to make a new sheet your counter will go up and your cell reference will change.

Upvotes: 1

Related Questions