Quick links/hyperlinks to applicable sheets

Jnb99

Board Regular
Joined
Mar 29, 2016
Messages
84
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.
 
Thank you @offthelip !

Ok there is some errors..
1. On the Edatabase It is creating what looks like a hyperlink in column A, but its not clickable. The doc number is blue and underlined, but nothing happens when clicked.
2. The same Document number of the new estimate being saved appears on the estimate template itself when it is saved, also in column A. That link is however clickable, but when clicked, it return a "reference is not valid" error.
3. And then lastly the new code is not transferring the client name (column C) to the edatabase, and the amount transferred is also 0.00 (Column D)

Thank you for your help!
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
It would appear that playing around with the order of your code upset something, (if it was working before). SO I have reverted to exactly what you had before and just added a few lines of code at the end so try this for the command click routine:
VBA 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

With Worksheets("Estimate")
  docno = .Range(.Cells(4, 6), .Cells(4, 6))
End With
With Worksheets("EDatabase")
 .Hyperlinks.Add Anchor:=.Range(.Cells(nextrow, 1), .Cells(nextrow, 1)), Address:="", SubAddress:= _
        docno & "!A1", TextToDisplay:=docno
End With

End Sub
 
Upvote 0
I've just realized something. The admin people changed the template today, or deleted some rows. Result being that the EstTot cell fell within the clearcontents range. Is there something that can be done to get around this by setting some sort of area. It doesn't even makes sense to me when I am reading it back to myself lol
 
Upvote 0
Success! It's working now, but the "reference is not valid" error is still coming up. I see now that the doc number on the Edatabase, is 1 higher than the sheet being saved. So it's calling a sheet that does not yet exist. Assume there is a sequence problem with this
With Sheets("Estimate").Range("f4") .Value = "E" & (Mid(.Value, 2) + 1)
piece of code. Need to happen after the data is transferred to Edatabase
 
Upvote 0
that can be fixed by moving a bit of my code like this:
VBA 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)

With Worksheets("Estimate")
  docno = .Range(.Cells(4, 6), .Cells(4, 6))
End With


Call saveAsPdf


With Worksheets("EDatabase")
 .Hyperlinks.Add Anchor:=.Range(.Cells(nextrow, 1), .Cells(nextrow, 1)), Address:="", SubAddress:= _
        docno & "!A1", TextToDisplay:=docno
End With

End Sub
 
Upvote 0
Solution
**Champagne popping** You my friend are a legend! Its working perfectly! Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top