How do I amend this code to .....

[Mikey C]

Hi all

Could anybody tell me how to amend the following code? Instead of
inserting the same link and text into the selected cell, I really need
it to create a link to the newest sheet (i.e. when the macro is run
more than once). So instead of linking to sheet "Template (2)'!A1"
every time, I would like it to increment.

Also, is it possible for the text to disply on the hyperlink to be
whatever is in cell A1 on the new sheet?


Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(47)
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Index").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:= _
"'Template (2)'!A1", TextToDisplay:="'Template (2)'!A1"

If I haven't described very well just shout!

Thanks

Mike

 

[Pete_UK]

I would have thought it might be better to rename the Template (2)
sheet to something else that can be incremented (eg Results_1,
Results_2 etc) and that you can do this after your code. Add this line
after your code:

rename_sheet()

and add this routine to your macro:

Private Sub rename_sheet()
' Renames Template(2) sheet to Results_1, Results_2 etc
'
Dim my_sheet As Worksheet
Dim my_count As Integer
Set my_sheet = Worksheets("Template (2)")
my_count = 1
On Error Resume Next
my_sheet.Name = "Results_" & my_count
Do Until Err.Number = 0
Err.Clear
my_count = my_count + 1
my_sheet.Name = "Results_" & my_count
Loop
End Sub

So, the first Template (2) sheet gets renamed to Results_1, If the
user then invokes the macro again Template (2) is created temporarily
and then gets renamed to Results_2, and so on.

I've not tested it fully in your situation, but I assume that Excel
will automatically change the hyperlink addresses.

Hope this helps.

Pete

 

[Mikey C]

Brilliant - thanks Pete!

 

[Pete_UK]

Brilliant - thanks Pete!

Thanks for the feedback, Mikey - glad to be of help.

Pete