Macro help

[Claireyscott]

Hi Mum,

Hope you are having a good day so far.

This is the message I just posted on t'interweb, can you pass it to whoever
you think might be able to help me!

'Hello,

Can someone help me, I am trying to create a macro in excel that allows me
to create a copy of a worksheet, clear its contents then hyperlink it to a
particular cell.

The problem is that I want it to link a different page to a different cell
every time i.e. create sheet 2(3) and link to R3. create sheet 2(4) link to
cell R4, but I can only make it so that it creates the copy, deletes the
contents and the links cell R2 with Sheet 2(2), at the moment the coding
looks like this:

Sheets("Sheet2").Select
Sheets("Sheet2").Copy Before:=Sheets(2)
Sheets("Sheet1").Select
Range("R2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Sheet2 (2)'!A1"

What would I need to add or change to make it do what I want to?'

 

[Mike H]

Hi,

Put this in a general module. It copies the active sheet and creates a
hyperlink to the copy in column R of sheet 1.

Sub stance()
ActiveSheet.Copy Before:=ActiveSheet
Myname = ActiveSheet.Name
Sheets("Sheet1").Select
Lastrow = Cells(Cells.Rows.Count, "R").End(xlUp).Row
Range("R" & Lastrow).Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:="'" & Myname & "'!A1"
End Sub

Mike