Print Worksheet and Hyperlinks

[dparteka]

I may be asking for a bit of a magic trick here. I have a worksheet that has hyperlinks to other workbooks. I’m looking to create an ActiveX command button to print the active worksheet and all the hyperlinks in column-D. The linked workbooks and the number of hyperlinks is not static, so the files being called and the number of hyperlinks do change, is this even possible? Thanks for looking, any help on the VBA code will be greatly appreciated.

 

[Chihiro]

Upload sample workbook. By print do you mean physical printout or PDF?

 

[dparteka]

Looking to send to a physical printer

 

[dparteka]

Here's what I pieced together, it works but is a bit long. The seven lines of code starting with the line that begins with "WO" are repeated for each cell in column-C that could potentially have a hyperlink, so the entire macro is 338 lines which isn't really much of an issue for me, on the other hand I'm guessing there might be a way to shorten it up? I put the error statement in because cells that do not have a hyperlink produce a #NUM so what happens is it opens and prints each until it errors out and then exits, this maybe unorthodox but it does work. Opinions and comments are welcome, thanks for looking.

Sub PrintPetitions()

On Error GoTo ProcError

  Dim WO As String

  WO = Worksheets("Audit Report").Range("C5")
  directory = "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\" & WO
  filetext = Selection.Value & ".xlsm"
  Workbooks.Open directory & filetext
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
  IgnorePrintAreas:=False
  ActiveWorkbook.Close savechanges:=False

ProcError:
  Exit Sub

End Sub