Macro Help Needed

[Claireyscott]

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?'

I can send you the spreadsheet complete with macro if you need me to.

Love Claire

 

[Peter T]

Hi Claire,

Not sure I follow, and wonder why you want to copy a sheet and delete all(?)
its contents rather than simply adding a new sheet.

I suspect the following is not quite what you want, but run it a few times
for ideas.

Sub test()
Dim i As Long
Dim sName As String
Dim rng As Range

Set rng = Worksheets("Sheet1").Range("R1")

sName = "Sheet2 (#)"

i = 1
On Error Resume Next
Do
Set ws = Nothing
i = i + 1
Set ws = ActiveWorkbook.Worksheets(Replace$(sName, "#", i))
Loop Until ws Is Nothing
On Error GoTo 0

Worksheets("Sheet2").Copy before:=Worksheets(2)

sName = "'" & Replace$(sName, "#", i) & "'!A1"

rng.Parent.Hyperlinks.Add Anchor:=rng.Offset(i - 1), _
Address:="", SubAddress:=sName
rng.Parent.Activate
End Sub

You'll find for this type of question you will get more or quicker responses
if you post in the ".excel.programming" group, unless of course it relates
to charts

Love Peter T