Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy hyperlink address of the same item code from MasterSheets to others sheets on the same workbook

0

Dear Sirs,

In the attached workbook I have

  • a master sheet of material with some of them has the hyperlink to the material folder.
  • other sheets which also have some material similar to the material in Master sheets
  • For each sheet in the workbook, I need to copy the hyperlink address of the same item code from the Master sheets to the same item code in each sheet
  • At the moment I do it repetitively  to manually to create the hyperlink to the same material code 

I need help in copying the hyperlink address of the same item code from the Master Sheet to each sheet in the workbook by using macros-vba. (It only copied if the item code in master sheets have a hyperlink)

Looking forward to your further help in this regards

Many thanks and best regards

Arsil Hadjar

Answer
Discuss

Answers

0
Selected Answer

Welcome back Arsil. The system I built differs from what you have in mind because I didn't like the idea of having to change several hyperlinks when there is a change in the address. Therefore I designed a system by which there is an extra sheet with 2 columns, Kode and Folder Address. Each address is listed only once and VLOOKUP is used to create hyperlinks wherever you want them. This is a sample of the formula for creating the hyperlinks.

=HYPERLINK(VLOOKUP("A0033",FolderList,2,FALSE),"A0033")

FolderList is a dynamic named range on the extra sheet. This name can be changed at the top of the sub CreateHyperlinks in the TeachExcel module (all code is there and CreateHyperlinks is the only procedure there you will have to run). You can change the name of the extra sheet, too. Note that you can run the code repeatedly on the same workbook, but be sure you have a safe copy until you know all the pitfalls. When I first tried the code the workbook got damaged beyond repair.

When you run the code for the first time the extra sheet will be created by the name of "Material Folders". The named range on it will also be created. The addresses from all existing hyperlinks on all sheets will be copied to the list which is then sorted. The code then removes all hyperlinks and replaces them with dynamic ones. Where column A contains only a Kode it will be used to create a dynamic hyperlink provided the Kode is found in the list.

Let me know if your trials uncover any errors in the code I overlooked.

Discuss

Discussion

Dear Variatus,

Many thanks for the help and the great idea.
 1. It works if I run the macro from the same workbook. perfectly. By this code, the copying process for all hyperlink from the Master Sheet to other sheets can be done in one click and very fast.
Great help as always.

2.  I tried to run the existing macro from my Personal.xlsb  and I found 
 -  the  material folder sheet is not created
 -  All Item code in all sheets with hyperlink indicating the value = #REF!
-   When I click the   = #REF!   it shows = HYPERLINK(VLOOKUP("A0001", FolderList, 2, FALSE),"A0001")

How to make the code also work if the macro is placed in my Personal.XLSB file

3. Just thought on another approach on the possibility of not defining all sheets name as shown below
' List the tabs which contain hyperlinks in column A, comma separated
    Const SheetNames As String = "Master,Steril,Syrup,Solid dan SIS,TollIn"

but using the approach of using 
 For Each Sheet In ActiveWorkbook.Sheets      
If Sheet.Name <> "Master" 

Then it calls the procedure to 
=HYPERLINK(VLOOKUP("A0033",FolderList,2,FALSE),"A0033")
 
Or should I create a new post on this alternative approach?.

Looking forward to having your further advice in this regards

Many thanks, Variatus.

Best regards
Arsil Hadjar
Arsil (rep: 32) Jun 22, '19 at 7:38 am
You might try changing all references to ThisWorkbook into ActiveWorkbook. There are two such references, both in the Function GetFolderList. One specifies that the sheet be created there, the other the named range. The reason why I specified ThisWorkbook is that I fear the implication of using ActiveWorkbook. You could create the sheet and name in any workbook which happens to be active. In fact, if you ran the code already from your Personal.XLSB a worksheet and range name was created there already. To control the danger you would need to specify additional conditions in the code which prevent the code from executing on a file that doesn't qualify. My code does little to enable such control.
Variatus (rep: 4889) Jun 22, '19 at 10:17 pm
Dear Variatus,

Many thanks for the solution.

By changing all references to ThisWorkbook into ActiveWorkbook from the two references, both in the Function GetFolderList, the code can be run from Personal.XLSB.
Again many thanks for the solution.

Best regards
Arsil Hadjar



Arsil (rep: 32) Jun 23, '19 at 8:53 pm
For Each Worksheet will include not only the Master but also the sheet created by the code as well as any other sheets you might add in the future. Enumerating the sheets you want examined by name is more precise and therefore gives you better control. I wouldn't consider the change and improvement. It's just lighting the fuse of a time bomb.
The program was written on the understanding that there could be old hyperlinks in any of the sheets, but especially the Master. So, you can add a hyperlink of the old format to a new file on any sheet and the new file name will  be added to the list and the hyperlink replaced with a dynamic formula. You can also just add the Kode number in column A of any sheet and the code will replace it with a dynamic formula provided the Kode exists in the folder list. The code is NOT designed to copy hyperlinks from Master to other sheets. It is designed to create a list of distinct link addresses and then create hyperlinks to them whereever the Kode appears in column A.
When the code is run repeatedly it will ignore cells in column A which contain a formula, which are blank (or contain only spaces), or where the cell value isn't listed in the Folder List.
Variatus (rep: 4889) Jun 24, '19 at 6:18 am
BTW, I found that some of your tab names contained leading or tailing spaces. You would notice that my code doesn't process such sheets even when their name is included in the list. That is because the program doesn't accept leading or tailing spces in tab names.
Variatus (rep: 4889) Jun 24, '19 at 6:19 am
Dear Variatus,

Many thanks for the additional explanation on
- How the code is designed and the reasons for enumerating the sheets name for more precise and better control.
- potential issues that might occur if the tab name contained leading or tailing spaces.

Best regards
Arsil Hadjar
Arsil (rep: 32) Jun 25, '19 at 8:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login