• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add hyperlink to other sheets from shapes added thru VBA

HanSam

Member
I am using below code to generate files based on unique values from a column on a raw data.
This is originally from Ron's work and I adapted it to my needs. My problem now is adding a link on the generated "buttons" on Home sheet. I have been on it for half a day now, and I can't make it to work. Can someone please help?
 

Attachments

  • Help.xlsm
    37.9 KB · Views: 4
You need to add two lines to your Copy_To_Workbooks macro in Module1.
Specifically, (1) you have a With pms…End With block and just before End With (line 280 of the module) you need to add:
Code:
.Parent.Hyperlinks.Add Anchor:=pms, Address:="", SubAddress:="'Previous Month'!A1"
and (2) you have a With cms…End With block and just before End With (line 251 of the module) you need to add:
Code:
.Parent.Hyperlinks.Add Anchor:=cms, Address:="", SubAddress:="'" & WSNew.Name & "'!A1"
 
Thank you. Got it working then saw your post. On the same code, do you know how I can generate the new files and the gridlines are off on those files for all sheets?
 
Turning off gridlines is one of those few cases where you need to select the sheets you want to process, because the command works on a window, not a sheet.
Probably the easiest is just before you save and close the file, you makes sure it's the active file and
Code:
    Set shtOrig = ActiveSheet 'store which sheet is the active sheet
    Sheets.Select 'will select all sheets as long as none are hidden
    ActiveWindow.DisplayGridlines = False
    shtOrig.Activate 'put the selected sheet back to what it was
 
Back
Top