+ Reply to Thread
Results 1 to 14 of 14

create hyperlink of range of cells in a column using worksheet event

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    create hyperlink of range of cells in a column using worksheet event

    Hello All, I have a worksheet event that does not seem to work and I am not sure why:

    My spreadsheet creates a number of order lines from Q2 to AY50 (could be any number of lines 5, 10 ... depending on the order), now in column S is the web address for the order location this gets copied on every line of the order (i.e. s2, s3...).

    Now I have a macro that copies this range of order lines to my tracker sheet but only copies as text so I edited the below code I found on the internet to change the range once it gets to my tracker sheet but it is not working, any ideas.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: create hyperlink of range of cells in a column using worksheet event

    Quote Originally Posted by iggypop View Post
    Now I have a macro that copies this range of order lines to my tracker sheet but only copies as text
    It would be better to fix the code that only copies text instead of making a worksheet change event macro. Show all the code that does your copy\paste.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: create hyperlink of range of cells in a column using worksheet event

    Get rid of your error statement so you can find out what the error is.

    "Address:=Target.Value", what would the outcome be for this?

  4. #4
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Thank you both for your quick responses:

    AlphaFrog - here is the copy/paste line, the problem is those cells are referencing other cells so if I do not paste values I just get a lines of #ref errors:

    Please Login or Register  to view this content.
    Hi Daveexcel, this is the error but I am not sure what it means - Run-time error '1004' Method 'Range' of Object'_Worksheet' failed
    Last edited by iggypop; 11-22-2018 at 06:00 PM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: create hyperlink of range of cells in a column using worksheet event

    Do a 2nd copy\paste for the S column and paste all

    Not Tested
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    it is giving me a PasteSpecial method of Range class failed on the xlPasteAll line

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: create hyperlink of range of cells in a column using worksheet event

    I don't know. Can you attach an example workbook with your code and it illustrates the problem?

    nAttach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  8. #8
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Thank you ALphaFrog, that will take a while to create a cleansed workbook, too many moving parts. Is there no way of doing my original ask, when ever new lines are pasted to the spreadsheet, it automatically hyperlinks any text in Column S, seems like the simplest solution.

  9. #9
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Update on the question, I was able to figure out the macro which works for a manual solution, stupid rookie mistake (worksheet was protected).

    Now hat I have done is used the hyperlink macro to create the below worksheet event which fires after a number of rows (varies) are pasted (values only because other worksheet is using cell references) from another worksheet. It worked perfectly the first 3 times then just stopped, any idea why?

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Hi can anyone assist, I find it really odd, that the above event would work exactly 3 times and not work again.

  11. #11
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: create hyperlink of range of cells in a column using worksheet event

    Your code makes no sense.
    ALphaFrog asked you to supply a sample workbook 5 days ago.


    Show the before and after results in your sample workbook.

  12. #12
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Hi DavesExcel/AlphaFrog, sorry, I understand, was just too much going on to create a sample workbook.

    Ok, please find attached the HW Orders workbook, note there are only two macros COPY to copy from HW Orders to Tracker (values only, this is because on the original workbook HW Orders rows have several cell references) and ADD_LINKS to create the links manually from the tracker sheet.

    Also in the Tracker sheet there is the code for a worksheet event that would do this automatically after the paste happens, this is what I am trying to accomplish or a way of adapting the copy/paste lines that the include the link when the rows are pasted
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: create hyperlink of range of cells in a column using worksheet event

    Again, don't need the worksheet_Change event procedure. Do it when you copy\paste

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 11-28-2018 at 08:47 PM.

  14. #14
    Registered User
    Join Date
    07-12-2013
    Location
    toronto, canada
    MS-Off Ver
    Excel 2007 & MAC excel 2011
    Posts
    46

    Re: create hyperlink of range of cells in a column using worksheet event

    Thank you Alphafrog, sorry for the late reply, it works, much appreciated.

+ 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: 0
    Last Post: 09-13-2017, 03:23 PM
  2. [SOLVED] Macros to Create New Sheet, Rename, and Create HyperLink to New Worksheet
    By jacksum in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2015, 12:44 PM
  3. How to make a Worksheet Calculate event when a range of cells change?
    By Invicta084 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2014, 07:20 PM
  4. need worksheet change event to fire when pasting; for all cells in paste range
    By ShoshanaM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-15-2012, 10:10 AM
  5. Vba to Create copy of worksheet and range of cells to used to calculate inputs.
    By Wyliecody in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2012, 04:48 AM
  6. Worksheet change event for range of cells and data validation
    By santosh123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-08-2012, 07:38 AM
  7. Worksheet Change Event-when a cell with a certain range of cells) are changed?
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2005, 07:06 PM

Tags for this Thread

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