Hyperlink Macro

Microsoft Office 2007 professional (aca...
June 14, 2010 at 07:19:57
Specs: Excel 2007
Hello every one.

i have the below workbook structure
I have a Summary Sheet and a variable no' of sheets (sheet are added each time a new entry is required)
The variable sheets are in serial ( labled 1,2,3,....), each time sheet is added it's labeled with it's sequence ( last sheet serial +1)
Each sheet has a row to summarize it in the Summary sheet, the row has a label in cell B1, this label is the sheet name

I have a macro that copy a sheet and insert it at the end of the sheets and give the sheet it's serial and add a row in the summary sheet and label it with the serial

what i need to do is to add instruction to the macro to add hyperlink to the row lable to the sheet it summarizes
So if the summary sheet cell B5 value is "8" then i need summary sheet cell B5 to have hyperlink to sheet "8"

Can this be done?

See More: Hyperlink Macro

June 14, 2010 at 07:51:48
It might help if we saw the code that you are using, but here's an example to get you started.

The code makes 2 assumptions:

1 - The last entry in Sheet1 Column B is the one that should become a link.
2 - The link should take you to last sheet in the workbook.

Sub LinkToSheet()
 lastBRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    Sheets(1).Hyperlinks.Add Anchor:=Range("B" & lastBRow), _
     Address:="", SubAddress:= "Sheet" & Sheets.Count & "!A1"
End Sub

Report •

June 14, 2010 at 23:13:36
Dear DerbyDad03

here's the code that i use

Sub NewEntry_June()
' NewEntry Macro


Selection.Insert shift:=xlDown
Application.CutCopyMode = False
Sheets("0").Copy After:=Worksheets(Worksheets.Count)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Name = Range("A1")

End Sub

First, For your assumption one : the last entry in sheet "Summary" is not the one that should be the link
There's always 5 rows after the last summary row

Second, if i understand correctly your macro is assuming sheet names are sheet1, sheet2, .....
if this is true then my sheet names are 1,2,3,......., only the serial without the word sheet
there sheet named "0" which is the template that i copy it and insert it at the end

Report •

June 15, 2010 at 02:05:04
Do me a favor.

Before I suggest any hyperlink code, make a backup copy of your workbook and try the code below. Does it do the same thing as the code you are using?

I tried to eliminate all of the inefficient Selections and Activations, most of which are not needed in VBA.

Paste the code into the VBA editor and use F8 to single step through so you can tell if any of the operations are wrong.

Sub NewEntry_Test()
' NewEntry Macro
' Edited By DerbyDad03
' 6/15/2010
Range("B13").End(xlDown).Offset(0, -1).Insert shift:=xlDown
Application.CutCopyMode = False
Sheets("0").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Range("B1") = ActiveSheet.Previous.Range("A1").Value
ActiveSheet().Name = Range("A1")

End Sub

Report •

Related Solutions

June 15, 2010 at 02:56:19

It works efficiently
Thanks and i knew my code was bad and that's because i'm still and beginner trying

Report •

Ask Question