Vba Copy sheet, rename from cell value and Add Hyperlink to that sheet

[Dave8899]

Hi All

I need some help please,

• I need to be able off a button press (i can create the Button etc.)
  • Add a New work sheet Report (copy of a pre-existing Report) Have code for this below.
  • Re-Name that sheet depending on the name in the current active cell
  • Hyperlink link the new report off the active cell.

Using th below tabel as an example i need toi be able to add R004 press a Button and a new work sheet is created with eh name of R004 fon the cell and that cell is then Hyperlinked to the report.

Hope that makes sense.

R001
R002
R003

So fare I have the below that creates a new report for me, where I’m running out of talent/knowledge. Is the AutoHyper link and the Naming the sheet off and the active cell, on the Master Sheet. i can change the name of the sheet with a Message window very practical.

Any help woudl be amazing, Thanks

Sub extrasheet()

Set ws = Sheets("Blank Report")

ws.Copy After:=Sheets("Report Types")

Set wsNew = Sheets(Sheets("Report Types").Index + 1)

wsNew.Name = "New Report"

End Sub

 

[Dave8899]

Hi I have got to this point now and it doing 99% of what i want i just nee to add teh Hyperlink link function any help woudl be great

Sub eNew_sheet()

Dim ShtName As String

Application.DisplayAlerts = False

On Error GoTo ErrMsg

ShtName = ActiveCell.Value2 ' <-- save the value of the ActiveCell

Set ws = Sheets("Blank Report")
 
ws.Copy After:=Sheets("Report Types")
 
Set wsNew = Sheets(Sheets("Report Types").Index + 1)
 
wsNew.Name = ShtName


Exit Sub

ErrMsg:

MsgBox ("That Report report already Exsists, Please chaeck and open a new Report"), , "Error Duplicate Repot"


Sheets("Blank Report (2)").Delete

   Application.DisplayAlerts = True


End Sub