Quick links/hyperlinks to applicable sheets

[Jnb99]

Hi everyone,

I have a quotation and invoice template, with a register for each and a client data base in one workbook. My code transfer the main data i.e. date, document number, client & amount from each invoice/quote to each register respectively. When the document is saved, it save a copy of the quote or invoice as a new sheet with the document number as the sheet name.

I would like to assign a hyperlink/quick access link to each of the document numbers on the register Automatically, to open that sheet when clicked on, instead of searching through all the sheets for it.

I hope this makes sense. Can add the code if needed.

Thanks in advance.

 

[offthelip]

You can do that quite easily using code like this:

docno = "Sheet2"
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A5"), Address:="", SubAddress:= _
        docno & "!A1", TextToDisplay:=docno & "!A1"

Obviously set docno to the document number used to create the sheet and change the reference toe A5 to the range where that document is in the register

 

[Jnb99]

I will try this tonight when I get home. Will this automatically add the kink to each new doc number that is added to the register?

 

[offthelip]

you haven't posted any of your existing code, so it won't do it automatically because I didn't know exactly what your current code is doing, but in order to integrate it into your current code you just need to set docno to the document you are trying to insert and set a range to the cell where you want the link and then change "Range("A5") to the name of the range you have set.

 

[Jnb99]

Here is my code

Private Sub CommandButton1_Click()
 
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Estimate")
Set WS2 = Worksheets("EDatabase")
' Figure out which row is next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important values to register
WS2.Cells(NextRow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)
Call saveAsPdf


End Sub
  
Sub saveAsPdf()
Dim saveLocation As String
    Dim rng As Range
    saveLocation = "C:\Users\***\Estimates\" & Range("f4").Value & Range("a14").Value & ".pdf"
    
    Set rng = Worksheets("Estimate").Range("A1:g50")
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
    
    Call saveSheetWithoutFormulas
    
End Sub
Sub saveSheetWithoutFormulas()
    Dim ws As Worksheet
'f4 is document number
    Set wh = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("f4").Value <> "" Then
    ActiveSheet.Name = wh.Range("f4").Value
    End If
    wh.Activate
With Sheets("Estimate").Range("f4")
     .Value = "E" & (Mid(.Value, 2) + 1)
End With
 Call clearContents
End Sub

Sub clearContents()
Range("a23:d43").clearContents
Range("f23:f43").clearContents
Range("a14").clearContents

End Sub

 

[offthelip]

It is not clear from your code where your "register" is, i.e where you want the hyperlink. I can see that you want a hyperlink to the sheet renamed by this code:

ActiveSheet.Name = wh.Range("f4").Value

but I can't see where you want the hyperlink? in wh.sheets("estimate").range("f4") ?? what about when the next invoice is generated presumably you wnat hte hyperlink put somewhere else??

 

[Jnb99]

Apologies. When the estimate is saved, the document number of the saved estimate is transferred to eDatabase sheet, with layout:

A B C D
1 Doc No Date Client Amount
2
3

So each time a new estimate is saved it adds to the eDatabase. So all the cells in Column A of eDatabase must have the links to its corresponding sheet i.e. the actual quote in the workbook.

 

[offthelip]

So do you have any code that is doing this , if so please can you post it.

 

[Jnb99]

So all the cells in Column A of eDatabase must have the links to its corresponding sheet i.e. the actual quote in the workbook.

This is the code that I need, for each new doc number to have a link

So each time a new estimate is saved it adds to the eDatabase.

this section above is this piece of the code below

Private Sub CommandButton1_Click()
 
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Estimate")
Set WS2 = Worksheets("EDatabase")
' Figure out which row is next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important values to register
WS2.Cells(NextRow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)

 

[offthelip]

I understand now, thanks try this modfication to the command click. I had to move the call to savesheetwithoutformula, because the worksheet needs to be created before creating the hyperlink

Private Sub CommandButton1_Click()
 
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim docno As String
Set WS1 = Worksheets("Estimate")
Set WS2 = Worksheets("EDatabase")
Call saveAsPdf     ' call this first so that sheet is created

' Figure out which row is next row
nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important values to register
WS2.Cells(nextrow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)
docno = WS1.Range("F4").Value
WS2.Hyperlinks.Add Anchor:=Range(Cells(nextrow, 1), Cells(nextrow, 1)), Address:="", SubAddress:= _
        docno & "!A1", TextToDisplay:=docno

End Sub