+ Reply to Thread
Results 1 to 71 of 71

Automatically copy hyperlink from another workbook based on a cell value

  1. #1
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Automatically copy hyperlink from another workbook based on a cell value

    Hi i want to ask for help regarding to my excel file. what i want to make is automatically copy the hyperlink from another work book based on cell value of master file. I attached the sample of excel data. thank you in advance.
    Attached Files Attached Files
    Last edited by civram1982; 02-13-2017 at 03:13 AM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    A few questions:

    1. Will every file except the Master be closed/open/could be either?
    2. Is each link to be updated immediately after a cell is edited? - ie one cell at a time - using change event macro
    3. What happens if link is not found - message box to user?
    4. Also please confirm my detailed understanding of your requirement:
    - links to be placed in cells in columns I,J,K,L,N,O,P of Master
    - taking one example (column I)
    a) lookup column I value is SHD-AR-00390-SHT 01 OF 02
    b) match that to value in the column C in file Sample 1
    c) copy the link embedded (in cell in same row) in column K
    d) embed that link (in lookup cell) in column I in Master
    e) cell text in column I = original look-up value
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    1. Will every file except the Master be closed/open/could be either? YES it will open and closed
    2. Is each link to be updated immediately after a cell is edited? - ie one cell at a time - using change event macro - YES it will be at a time.
    3. What happens if link is not found - message box to user? YES
    4. Also please confirm my detailed understanding of your requirement:
    - links to be placed in cells in columns I,J,K,L,N,O,P of Master
    - taking one example (column I) YES thats where link will be placed
    a) lookup column I value is SHD-AR-00390-SHT 01 OF 02
    b) match that to value in the column C in file Sample 1
    c) copy the link embedded (in cell in same row) in column K
    d) embed that link (in lookup cell) in column I in Master
    e) cell text in column I = original look-up value

    thank you for your response

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    So that VBA can be told where to find the files and sheets, a few more questions:

    1. Are all the files in the same folder?
    2. Is the Master in the same folder as the other files?
    3. Is the name of the lookup sheet in each file "Sheet 1"
    4. If name is not "Sheet 1", what is its name? (Do they all have the same name?)
    5. Is the lookup sheet the ONLY sheet in each file?
    6. is the lookup sheet the FIRST sheet in each file?

    thanks

  5. #5
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    1. Are all the files in the same folder? No data in the link is in the different folder but excel files is in the same folder
    2. Is the Master in the same folder as the other files? Yes
    3. Is the name of the lookup sheet in each file "Sheet 1"? No in one workbook there is 4 to 5 worksheets with different name like (Civil, Architectural, Electrical, Mechanical, and others)
    4. If name is not "Sheet 1", what is its name? (Do they all have the same name?) No answered in no. 3
    5. Is the lookup sheet the ONLY sheet in each file? In master file there is only one sheet but in other sample it composed of 4 to 5 worksheets
    6. is the lookup sheet the FIRST sheet in each file? No there are 4 to 5 worksheets

    thank you
    Last edited by civram1982; 02-13-2017 at 10:50 AM.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    In the 6 sample files there was only one sheet per file.
    Now you are saying that there are 4 or 5 worksheets in each workbook.

    In which sheet is the link to be found?
    "Civil" "Architectural" " Electrical" "Mechanical" "others"??
    Or could the link be in any of the sheets?

  7. #7
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Actually the real number of sheets in every workbook are:
    Sample 1 - 6 worksheets (Civil, Civil-SKT, Architectural, Electrical, Mechanical, General - Sheet Name)
    Sample 2 - 5 worksheets (Civil, Architectural, Electrical, Mechanical, Others - Sheet Name)
    Sample 3 - 4 worksheets (Civil, Architectural, Electrical, Mechanical - Sheet Name)
    Sample 4 - 1 worksheet (MIR - Sheet name)
    Sample 5 - 1 worksheet (RFSI - Sheet name)
    Sample 6 - 1 worksheet (EC - Sheet name)

    Yes you are right the link is in any of the sheets.

    I tried index match but the problem is copying the link if the reference is match.

    Thank you

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I am trying to come up with a non-complicated way to tell VBA to search for the text, find the matching cell and then go to the correct column for the link in all 6 files in 18 worksheets - which are not laid out in a consistent manner.
    - the column for the match is not consistent (columns B or C in the cut-down files)
    - the column containing the link is not consistent (columns J,K,L or N in the cut-down files)

    I have an idea which may work - but I must understand where we are searching

    From your sample files it appears that we search only specified workbooks for values as follows
    I = Drawing search only in Sample 1
    J = MS/ITP search only Sample 2
    K = MATERIALS APPROVAL search only Sample 3
    L = IMIR search only Sample 4
    N = MOCK UP search only Sample 5
    O =MEP CLEARANCE search only Sample 6
    P = RFSI search only Sample 5
    Is this correct or could we find any of the matches in any workbook?


    If we search for the link for "Drawing" we always search in "Sample 1" is this correct?
    so we search for SHD-AR-00390-SHT 01 OF 02
    How many times could we find a match in Sample 1
    - once? or possibly more than once?

  9. #9
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Yes you are right because in every workbook the number of columns is different. But all the links that need to be copy is in the status column.
    Yes you are correct about the searching I,J,K,L,N,O,P have a designated workbook.

    Yes we will search dwg only in sample 1 but it depends on the discipline(ex. CV,SKT,AR,EL,ME,GE)
    searching depends on how many revision sometimes there 4 same reference the difference only is the revision.

    thank you
    Last edited by civram1982; 02-13-2017 at 01:27 PM.

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Based on what you have told me,I expect this to produce only one possible match:

    search for Drawing
    SHD-AR-00390-SHT 01 OF 02-00

    look in workbook "Sample 1" for
    SHD-AR-00390-SHT 01 OF 02
    with revision number
    00 in the next column

    and then get the link from
    Column with Heading "Status"

    BUT - "Sample 3" is different to every other file - the column is not named status (it is called "Action Code") and and headings are not in row 3

    There are too many inconsistencies in your files to make this job easy!

    Is it possible to amend all sheets so that the headings are always in row 3 and the links column is always named "Status"?
    Then VBA
    - can search for text + revision number = ROW
    - look for column with value "Status"= COLUMN
    simple

  11. #11
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Yes you are right. Sample 3 is very complicated because this workbook is different in other workbook their revision is through horizontally not like the other workbook their revision is vertically.

    sample 1,2,4,5,6 the worksheets and style is almost the same the difference is the number of columns. And all that worksheets is in a table and i specify a name in every column.

    sample 1,2,4,5,6 is always in row 3 and column name is status. But sample 3 i can't amend it because this is the required settings.

    Thank you
    Last edited by civram1982; 02-13-2017 at 02:40 PM.

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    The problem can be addressed more simply by thinking differently

    How?
    Create a NEW workbook to contain only column A = lookup value (incl revision no) , and column B = link

    What is required?
    - 6 work books currently contain all the information
    - Put a macro in each workbook to transfer the values to the new file
    - Every time each workbook is saved then the latest values should be transferred across

    Then
    Looking up the values in the new workbook is easy - formula = vlookup(correct value in Main,A:B in NEW,2,0)

    simple

  13. #13
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    meaning the master data only get the information in the new workbook. but how do i make a macro in each workbook and transfer the values to the new workbook?

    it is possible that this new workbook is hidden because all excel files is shared in a server.

    thank you very much!

  14. #14
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    We will work on the macros together - it will not be difficult.
    Let's do everything tomorrow.
    I will send you questions for Sample1 by 10.15 (UK) time tomorrow
    When Sample 1 macros works then you can copy and modify macros for Sample 2, 4, 5 6 (-they will be very similar)
    Then we will sort out Sample 3

    Yes - you can hide the file, or protect is so that nobody can see the contents or change it.


    Kev

  15. #15
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you very much!

  16. #16
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    To make things simpler, let's just add a hidden lookup sheet in each of the 6 files (Sample 1, Sample 2 Sample 3, Sample 4, Sample 5 and Sample 6)
    Then everything is contained in each file and we do not need to hide a new file

    The table in each file will consist of 2 columns
    Column A = Reference-RevisionNo (to match Master values)
    Column B = Link
    The table will be auto-refreshed every time each file is saved and so will be up-to-date when lookup from Master happens

    What I need from you:
    Download the attached workbook and complete the first 5 sheets (like sheet Example )
    - check that the worksheet names are all there and all correct (they are the ones you gave me in post#7)
    - add the column number for the reference lookup in each sheet
    - add the column number for the link (column STATUS) in each sheet

    Please confirm that "REVISION" column is always the next one after "reference lookup" column for all sheets in all workbooks(except Sample 3)

    We will deal with Sample 3 later - requires different treatment

    I am offline now until 10.30 UK time - can you attach the completed file by then?
    Thanks
    Attached Files Attached Files
    Last edited by kev_; 02-14-2017 at 04:04 AM. Reason: forgot to attach file!!

  17. #17
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Yes all revision number is after the reference lookup

    Thank you
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Thanks for the file - it is what I wanted.
    I will build the VBA in stages, and you can test it for me at each stage.
    I will post the first stage by noon UK time

    I notice that within each workbook the reference column is the same for every sheet and also the link column is the same for every sheet
    Can we use that as a general rule?
    (it makes the VBA require fewer variables and keeps thing simpler )

  19. #19
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    yes its all the same.

    thank you very much!

  20. #20
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    In that case I think we can do everything from master without creating a lookup sheet in each file.

    I need to modify the VBA - try again in 45 minutes

  21. #21
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Apologies for delay - domestic crisis!

    Test 1

    Try this - it copied correctly the links on what I tested. But with your files we will see...

    1. Put this code in the Master's SHEET module (not a general module)
    (later this will be an event macro triggered when changes are made to cells - so must be in the same sheet as those cells)
    2. Open all the Sample files except Sample 3
    (we will test with closed files later)
    3. Go back to Master and select a cell where you want link added
    4. Run the macro
    5. Test for columns I to P except K

    At the moment this is searching through all the cells in all worksheets, after testing I will get it to exit as soon as it finds a link
    It will be faster to use the match function - I am experimenting with that at the moment

    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you.

    I tried in my file but i have an error message of "subscript out of range".

  23. #23
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    i trace the error it was my excel file name and now its working.

  24. #24
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    That was a good first test

    Amended code to test. I suggest you copy and paste all the code
    I have highlighted in colour where changes were made

    This should now work with open and closed files.
    It assumes that the files are found in same folder as Master
    If a file is found closed, it should be closed after use
    If a file is found open, it should be open after use

    Please test again



    Now replace the code with this:
    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you. its working..

  26. #26
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I have now split the VBA into 2 subs.
    Note that some variables are now declared at the top
    - which makes them available to all subs in the module and their values are retained between subs

    So replace everything with the code below and test it
    thanks


    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    what do you mean this code "lookupFile = "TestSampleLinksTable.xlsm""

    its working if i removed that code.

  28. #28
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    oops - that was my test file - sorry!!

    Is the VBA giving you exactly what you want?

    Sample 3
    I am now thinking about "Sample 3"
    - this is more tricky
    - the revision number is not there to use as a matching value

    The formula to calculate the column number in Sample 3 is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    so
    revision 0 is 12 + (0 X 4) = 12 = column L
    revision 1 is 12 + (1 X 4) = 16 = column P
    revision 2 is 12 + (2 X 4) = 20 = column T
    revision 3 is 12 + (3 X 4) = 24 = column X

    But how do we search?
    In the Master, the SearchString is KP-A269-MAR-AR-00071-00
    which does not appear in Sample 3

    So we need:
    ROW no - comes from a match for KP-A269-MAR-AR-00071 in column E
    and
    COLUMN no - we use -00 plus formula above

    Do you agree?

    Does every reference in Master always end with " - " and 2 digit number?

  29. #29
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    i see.. yes its definitely what i want.

    yes i agree.

    yes its always end with the revision number.

    thank you

  30. #30
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Good

    Because of having to calculate the column number for Sample 3, and to avoid converting it back to a letter, it will be better to use Cells(row,column) notation instead of Range("A1") notation. I will alter the code for this.
    I also need need to amend the code slightly to take account of how we have now agreed to lookup Sample 3.
    I will post the complete amended code for you by 1pm UK time tomorrow.

    Kev

  31. #31
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you very much.

  32. #32
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Modifications made:
    1 It is now an event macro - works with double click (less annoying for testing - but can amend to run when value changes if you prefer)
    2 "Sample 3" lookup added
    3 Code sub-divided down into smaller subs
    4 If it appears to have stopped working, your testing may have caused Events to be disabled - run macro StartEvents to re-enable
    5 Message appears if either a link is blank or a match is not found

    It works perfectly for me.
    Test it with:
    - valid references
    - references that will not match
    - references where links are missing
    - perhaps some extra testing for sample 3
    Make sure you test it for whatever values VBA is likely to come across with live data

    Delete all previous code and replace in same sheet module with:
    Please Login or Register  to view this content.
    Last edited by kev_; 02-15-2017 at 08:06 AM.

  33. #33
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    its working perfectly. i try to my actual file and its working.

    i prefer the change event and i have a question about when the excel file is close when i double to run the macro i notice that the excel is running in the background. But all in all it perfect!

    can you explain the below code:
    refType = "Materials Approval"
    refNo = Right(searchStr, 2)
    searchStr = Left(searchStr, Len(searchStr) - 3)
    refCol = 5
    linkCol = 12 + (refNo * 4)
    firstRow = 9


    thank you very much!
    Last edited by civram1982; 02-15-2017 at 08:52 AM.

  34. #34
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I have tried to give you something that you can adapt if things change in the future
    Also I have tried to make the basic code work for every file - hence having the "Special case" section to modify the values before they hit the basic code.

    refType = "Materials Approval"
    To tell VBA that it had to treat items in column K differently, the column header (=Materials Approval) was used to make it clear
    Could also have also used IFlookupFile = "Sample 3.xlsx" OR IF targetCol = 11
    (but is Sample 3 a real file name? and why is colum 11 different - perhaps more helpful in 6 months than now!)


    refNo = Right(searchStr, 2)
    VBA needs this value (= revision no) for Sample 3 only
    The formula to work out the column to find the link is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The revision number is the last 2 characters of the value searched for (cell value in Master)
    RefNo is the rightmost (ie last) 2 digits of the reference we are looking up (ie the Revision No)
    refNo = Right(searchStr, 2) = the last 2 characters of searchStr
    where searchStr = the cell value of the cell to add the link to
    as defined in this line in the code:
    Please Login or Register  to view this content.

    searchStr = Left(searchStr, Len(searchStr) - 3)
    is simply the value in cell in Master except the last 3 characters (= hyphen + revision number)
    This is required to be specified differently for sample 3, because nowhere in Sample 3 is the revision number held as a separate value

    refCol = 5
    this is column E in Sample 3, where the match is found

    linkCol = 12 + (refNo * 4)
    see above and and explanation in post#28


    firstRow = 9
    All files except Sample 3 have data starting in row 4
    Sample 3 data starts at row 9 (in the file you posted)
    Last edited by kev_; 02-15-2017 at 11:50 AM.

  35. #35
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    i prefer the change event
    Rename the macro to:
    Please Login or Register  to view this content.
    From:
    Please Login or Register  to view this content.
    to create a trigger when the value in the cells in Master are modified


    The line below prevents a trigger when the cell value is clearly invalid
    Please Login or Register  to view this content.
    The minimum a cell needs is
    Revision number = 2 digits
    Hyphen = 1 digit
    File name = minimum 1 digit
    1+1+2=4
    And (more importantly!) the code would fail if there are fewer than 4 characters because of how VBA determines the search string for Sample 3. So preventing BAD values entering the VBA avoids requirement for handling runtime errors in the code
    Last edited by kev_; 02-15-2017 at 12:06 PM.

  36. #36
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    actually after ask you i already get the answer i just want know the explanation if i am right. now i understand very clearly.

    and also excel name is different in the actual file i already try in my actual file and its working perfect the way the data i want to be.

    change event is also working. one more thing i notice is how to stop excel from flickering when running the vba code.

    thank you
    Last edited by civram1982; 02-15-2017 at 12:23 PM.

  37. #37
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    To get rid of the flickering:

    Amend the code like this

    insert this line:
    Please Login or Register  to view this content.
    before this block
    Please Login or Register  to view this content.
    and this line:
    Please Login or Register  to view this content.
    after the same block

    Please remember to mark the thread as solved - if you have any further question you can still ask them on the thread after it has been marked as solved

    Kev
    Last edited by kev_; 02-15-2017 at 12:33 PM.

  38. #38
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you very much. i will mark the thread as solved. again thank you..

  39. #39
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    You are welcome

    Tell me how long does it take to add a link using the real files ?
    1 second
    2 - 5 seconds
    over 5 seconds

  40. #40
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    over 5 seconds cause i have so many data in the actual file.

  41. #41
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    OK
    You have a solution - but I think that is too slow.

    This is how we could speed it up using the work we have already done, and without creating a file that needs hiding on the server or putting macros into all the other files:

    1. Add a new sheet to Master
    2. Sheet to contain 2 columns = Reference and link for everything
    3. Sheet is updated every time it is opened (or whenever you want)
    4. Instead of looking up the other files it looks up new sheet in Master
    5. Will also be able to use vLookUp or Match functions which are much quicker than running through each cell
    (the reference will include the revision number for everything including Sample 3)
    6. If it cannot find the answer in that sheet (can only be recent additions since update or errors) then it could look up the original file

    The look-ups will take less than a second
    I have all the information I need to do this and will send you the code to try tomorrow

    Kev

  42. #42
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    thank you..

  43. #43
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    As promised here is the code to create a table of lookup values in Master
    At the end of the routine, a message box pops up with the details of workbook names,sheet names and lookup ranges used.

    NOTE - when you test this on your live data for the first time choose a time when you do not need your computer for a few minutes!
    It is difficult to guess how long it may take to run.

    How the VBA works
    - 6 workbooks are opened in sequence
    - each cell in the relevant range in every sheet in every workbook is looked up
    - that provides the hyperlink address and (by offset) also
    - provides the reference
    - a table of those values plus sheet names and workbook names is created

    For you to do:
    1. Create a new sheet and name it "CreateLookupTable"
    2. Put the code below in its sheet module
    3. Test it with first with files named "Sample 1-6" first
    4. Test on your live data
    (To test with live data, replace file names in the 6 lines beginning Const File1 = "Sample 1.xlsx")
    5. Check the message box values to see if the lookup range for each sheet is reasonable
    - they are determined automatically so they may not be the optimum size
    - the range for File3 is all the data from row 9, the ranges for the other files is a single column

    What I want to know:
    1. Does it work for you - it works perfectly for me
    2. How long does it take to run on live data
    3. Are the ranges reasonable?

    Next:
    (if this test is successful!)
    - the change event macro in the other sheet to be modified to point at the new table

    Put the code below in sheet module of new sheet CreateLookupTable in file Master
    Please Login or Register  to view this content.

  44. #44
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    What I want to know:
    1. Does it work for you - it works perfectly for me. Yes it works perfecly.
    2. How long does it take to run on live data? It takes around 15 mins. in actual data.
    3. Are the ranges reasonable? Yes

    Thank you!

  45. #45
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I have re-written the code so more of the data extraction is done within Master. I will test it tomorrow morning and then post it for you - I hope it will reduce the running time for you.

  46. #46
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Code as promised
    - this code does more of the work inside Master itself
    - timer added to tell you how long it takes to run
    - sheets 12456 and 3 may look a little untidy - but that does not matter
    - final values are placed in sheet Table2
    - it works perfectly for me

    For you to do:
    1. Add a new sheet in Master named "Table2"
    2. Place the code below in Table2 sheet module
    3. Add 2 more new sheets named "12456" and "3"
    4. Test it on your sample files first
    5. Test on your live data
    (To test with live data, replace file names in the 6 lines beginning Const File1 = "Sample 1.xlsx")

    What I want to know:
    1. Does it work for you?
    2. How long does it take to run on live data?

    Please Login or Register  to view this content.
    Last edited by kev_; 02-20-2017 at 08:40 AM.

  47. #47
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    What I want to know:
    1. Does it work for you? Its working but i found an error and i can't find that error.
    error.png
    2. How long does it take to run on live data? its very fast but after that error it will stop.

    Thank you very much for your help.
    Last edited by civram1982; 02-20-2017 at 10:41 AM.

  48. #48
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I think it is probably failing when building the table at the end

    1.Does sheet "3" have values?
    2.Does sheet "12456" have values for the 5 workbooks?
    3.Does sheet "Table2" have any values? If so how much does it contain
    - it should begin with details from sheet "12456"
    - it should finish with details from sheet "3"

  49. #49
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    1.Does sheet "3" have values? Yes
    2.Does sheet "12456" have values for the 5 workbooks? Yes
    3.Does sheet "Table2" have any values? If so how much does it contain? Yes but it contains half of the data only.

  50. #50
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    3.Does sheet "Table2" have any values? If so how much does it contain? Yes but it contains half of the data only.
    - the values from "12456" are placed in the table first and they should be in workbook & sheet sequence.
    - if you compare "Table2" with "12456" it should be easy to discover at which value the code stopped - I think everything should be in the same sequence and so you should see all the workbooks and worksheets listed

    Where exactly did it stop?
    Was all the data on "12456" transferred?
    - if not, where did it stop?
    at a particular workbook?
    at a particular worksheet?
    part way through a worksheet?

  51. #51
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    i try to toggle breakpoint:

    Please Login or Register  to view this content.
    in that code after 95 row in table 2 it will send error type mismatch.

  52. #52
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    So are you saying that it completed everything for "12456?

    Were there any values for "3" in Table2?
    Last edited by kev_; 02-20-2017 at 05:09 PM.

  53. #53
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Yes it completed everything in "12456"

    No values of "3" in Table 2 the only values there is from the sample 1 which is only 95 rows.

    Thank you!

  54. #54
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    And "3" should be a messy worksheet with links in columns 12,16,20 - please confirm that there is at least one link in the sheet - I am sure there will be if Sample 3 was in the folder.

    Why only Sample 1 entries? - are Sample 2,4,5 & 6 not in the Master folder?
    - they must be in the folder otherwise the code would have stopped earlier

    So we need to work out why the code stopped after Sample 1
    - the message suggests there is a variable type that is mismatched
    Last edited by kev_; 02-21-2017 at 04:55 AM.

  55. #55
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    yes in "3" the worksheet is messy. yes 12,16,20 has a link.

    yes sample 2,4,5 & 6 is in the one master folder.

    is the filename of master excel matters. i mean if i change to actual filename do i need to change in this code
    Please Login or Register  to view this content.
    .

    thank you.
    Last edited by civram1982; 02-21-2017 at 04:49 AM.

  56. #56
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    FIRST: Please confirm that the values for Sample 2,4,5 and 6 are present in "12456" - if they are then the problem is happening when the data is being transferred to Table 2

    The VBA is stopping after putting the entries for Sample 1 into the table - which is peculiar
    - all values from 6 sample files have all been added to the 2 sheets before this in the code
    - so there is a variable value that VBA says does not match the variable type defined in the Dim statements when it is transferring from "12456" to the table

    The VBA below has been amended so that we can see where VBA is stopping in more detail

    Add a new worksheet in Master and name it "Details"
    - this will hold all the values as VBA tries to create entries in "12456".

    After running the new code below you need to look at the LAST row in sheet "Details" and find that in "12456"
    - the value in column A in "Details" tells you which row is the current row
    - the error is caused either by something in that row or in the next row with a link in "12456"
    - is this the beginning of Sample 2 values?
    - does anything look wrong/different?

    Replace old code with this

    Please Login or Register  to view this content.
    Set master = ThisWorkbook
    masterPath = master.Path & "\"
    You do not need to do anything with this - it is fine
    Last edited by kev_; 02-21-2017 at 05:46 AM.

  57. #57
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Yes all the data of sample 2, 4, 5 & 6 is present in "12456".

    No is not the beginning of sample 2. that data still from sample 1.

    i notice some negative(-1, -2) value in revision column
    Last edited by civram1982; 02-21-2017 at 06:16 AM.

  58. #58
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I do not think that is causing the code to fail.
    I forced negative -05 into one of the cells and
    - VBA completes successfully
    - Table2 shows the reference as SHD-AR-00390-SHT 01 OF 02--05
    (the reference may not exist, but the code does not fail with a negative for me)

    I did notice that I forgot to declare 2 variables. Please add this line under "Declare Variables"
    Please Login or Register  to view this content.
    Run the code again - it should not behave any differently, but it is worth a try.

  59. #59
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    the problem still the same after 95 row in table 2 it will send error same error before.
    Last edited by civram1982; 02-21-2017 at 08:31 AM.

  60. #60
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Please attach Master workbook saved after the code has failed
    I need to look at sheets Table2 , "12456" and "3" and run those values against using the VBA on my PC - I hope the code fails on my PC too!!

    thanks

  61. #61
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    I am glad to say that the code has stopped at the same place on my PC - I am now investigating.

    It is very unusual for VBA to successfully process 94 lines of data and then stop - the data looks consistent and looks clean.
    It is a puzzle
    It may take a while for me to understand why this is happening....

  62. #62
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    no problem i will wait.

    thank you very much!

  63. #63
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Good news - I have discovered what is causing the problem.

    VBA loops through all hyperlinks in sheet "12456" with
    Please Login or Register  to view this content.
    This line tells VBA where to find the hyperlink in the worksheet
    Please Login or Register  to view this content.
    For most hyperlinks this works but
    - a few of the h.Range.Address values are a range of cells
    - it should always be a single cell
    - the VBA was allowed to run and ignore errors
    - the entries in column A of sheet "Details" then looked like this:

    Error.jpg

    - these values are used by the code to look to adjacent cells for Sheet Name and Workbook Name and the reference - the invalid range causes the VBA to fail

    I believe this is a bug in Excel - these are not values generated by VBA.
    - VBA is asking a question and Excel is giving an invalid answer
    - in the picture lines 95,96 & 97 cannot all be true - each cell has a different link

    Fortunately sheet "12456" contains everything we need , but I must write the code in a different way to bring it into Table2

  64. #64
    Forum Contributor
    Join Date
    11-17-2013
    Location
    Qatar
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Hi Kev,

    I'm following this discussion but unfortunately when I tried to copy paste the code nothing happen when I was opened/closed the files.
    Would you like to upload the files with the code contents already please?
    Thanks in advance for your helps.

    Regards,
    Iqballhud

  65. #65
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    @Iqballud - I am not sure I understand. Including the code in a workbook will not make it work any differently to what you have tried.
    The code in this thread is unlikely to work without adaptation to your particular needs. It is very specific and is driven by the particular structure of the files being interrogated. Given that fact and also the length of this thread, at its conclusion I will post a general code which could be adapted by anyone. I will try to remember to PM you when I do that.
    if you have a similar issue that you are trying to resolve it would be better that you start a new thread.

  66. #66
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    @civram1982
    As promised, amended code.

    To avoid the previous problem cells with links are now found with:
    Please Login or Register  to view this content.
    To make the code simpler:
    - the values for sheet "3" are now added to the bottom of sheet "12456" and
    - values are then transferred from "12456" to Table2

    Message box added at end to display cells without links
    Code told to skip "hidden" worksheets

    You need to:
    - replace previous code with code below
    - delete sheet "Details" (no longer required)
    - test it

    What I need to know:
    - did it work
    - time taken to run

    Next step:

    - if test successful change event macro required to look up the reference values in Table2

    Please Login or Register  to view this content.

  67. #67
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    it works perfectly and runs in seconds.

    how about when i update the sample 1-6 i mean if i enter new the data. Is table 2 automatically add new data also?

    thank you so much for your help!
    Last edited by civram1982; 02-22-2017 at 09:30 AM.

  68. #68
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    Good - I am very pleased that it runs quickly with your real files
    Before I answer your question, I need to understand how you work.

    1. After updating a live file ( the "real" Sample 1 to 6) do you:
    - not SAVE Sample(1-6)
    OR
    - SAVE Sample(1-6)
    OR
    - SAVE and CLOSE Sample(1-6)
    OR
    - any of the above

    2. Does anyone else amend Sample (1- 6)?

  69. #69
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    1. Always Save sample (1-6)
    2. Yes

    and also can i hide table 2, 12456, 3 so that no one can edit that sheets.

    Thank you!

  70. #70
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Automatically copy hyperlink from another workbook based on a cell value

    So how about a macro that updates the value in Master whenever Samples1-6 is saved?
    - requires a simple macro in all 6 files
    - if you want to do this then perhaps better to have 6 lookup sheets in Master (otherwise macro has to update all 6 files whenever only one changes)
    - I can tell you how to do this and you can create each macro
    - the first thing to do is to create 6 new sheets in Master with the same names as the 6 files

    and also can i hide table 2, 12456, 3 so that no one can edit that sheets.
    Yes - good idea
    Last edited by kev_; 02-23-2017 at 06:58 AM.

  71. #71
    Forum Contributor
    Join Date
    12-01-2013
    Location
    Saudi
    MS-Off Ver
    Excel 2007
    Posts
    103

    Re: Automatically copy hyperlink from another workbook based on a cell value

    hi kev,

    In master file i add 6 sheets and also how about the table 2, 12456 and 3 i will delete this sheets?

    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-11-2016, 04:56 PM
  2. VBA to copy a cell value from one workbook to another workbook automatically
    By kpower in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2015, 12:45 PM
  3. How to automatically copy cell with hyperlink from one sheet to the other
    By shalhevet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2013, 05:48 PM
  4. Automatically save file with cell value + copy cell values to other workbook
    By notquitetheguru in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2013, 07:04 AM
  5. Replies: 4
    Last Post: 09-15-2012, 02:18 PM
  6. [SOLVED] copy active sheet, create new workbook, name new workbook based on cell reference
    By jm0392 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-20-2012, 07:12 PM
  7. Copy hyperlink from one workbook based off user defined selection on another.
    By Qppg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-16-2007, 03:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1