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

Dave8899

New Member
Joined
Jan 17, 2019
Messages
32
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


VBA Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Dave8899

New Member
Joined
Jan 17, 2019
Messages
32
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
VBA Code:
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
 
Last edited by a moderator:
Solution

Forum statistics

Threads
1,148,259
Messages
5,745,715
Members
423,969
Latest member
seanguerrero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top