Create Range Of Hyperlinks Using VBA?
Aug 20, 2013
In column A I have a range of file path extensions (i.e., C:UsersJSmithDocumentsModelsHic Sunt DraconesTest.xlsx). What I want is a macro that will use those file path extensions to create hyperlinks in column B.
So far this is what I have:
VB:
Sub Hyperlink()
Range("a1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Range("b1").Value, SubAddress:= _
"", TextToDisplay:="Link"
End Sub
This code works for the first cell, but does not work for the rest of the cells in the range. Also. Keep in mind that the range of cells in column A is dynamic - it changes in length.
View 5 Replies
ADVERTISEMENT
Aug 12, 2009
I have two sheets. Sheet A has a list of folders. This list is updates dynamically every time the sheet is opened. I have another sheet (sheet B) which has a list of all the files in the sub-folders, listed with the folder name at the top of the column and the files within in the column underneath with hyperlinks to them. The goal is to be able to navigate to the folders on sheet A and to click there to go to the column where all the files in that folder are linked to. What I need is a macro that will search a column and for every cell that has text in it and create in the column directly to the right a hyperlink to the appropriate cell in the top row in sheet B that has the same name as the text in the cell on sheet A that it finds. I already have the macros for listing the folders on sheet A and the macro for listing all the files in the sub folders in sheet B and they work fine. The goal is a link list which is dynamic and that recreates itself no matter what I add to the folders.
For instance, let’s say there is a folder timesheets. If I add a sub-folder called
accounting to the folder Sheet A scans then timesheets would be bumped down one spot on the list, so the macro has to look dynamically for the text in the column on sheet A.
Also the addition of the new folder would move the order of the columns on sheet B, since both lists are alphabetical. So the link generation macro would need to search row 1 of Sheet B to find the match for the text in the cell to the left of where the link would be generated and create a link to the top of that column. I’ve looked all through the site and not found something that I can even modify to do what I need.
View 9 Replies
View Related
Jul 17, 2008
I currently have a macro that uploads information on a daily basis for hours worked on campaigns per employee. It then converts the data into a pivot table where it uses relative references to copy the total from the bottom and paste them on to another worksheet. I am now trying to use hyperlinks to link the total back to the pivot table. With daily updates though, the data is constantly being pushed down and is located in a new cell each time. How can I get a hyperlink to work for an area thats constantly changing?
View 9 Replies
View Related
Feb 9, 2010
This code finds and matches the Text value of a Shape to the value of a cell on seperate worksheets within the same workbook.
View 9 Replies
View Related
Feb 10, 2010
I want to loop through a list of numbers and add a hyperlink to each number. This hyperlink refer to a sheet with the same name as the number. how I shall use hyperlink line below. The loop and everything else is ok. I use a loop like the one below and want to refer the Hyperlink to activecell each time
View 2 Replies
View Related
May 21, 2014
I would like to set up an index sheet the will contain hyperlink to all the other sheets. Is a new sheet is added or deleted the index sheet must be updated
View 5 Replies
View Related
Feb 26, 2014
I am trying to place a hyperlink on each worksheet to an Index worksheet. This is what I come up with so far, and it does not work.
Code:
Sub CreateIndexHyperlinks()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Hyperlinks.Add Anchor:=ws.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
Next ws
End Sub
It successfully puts the words "Back to Index" into cell H8 of every worksheet, but this cell does not link back to the Index sheet (which is called "Index"), the way I want it to.
I don't really understand the Address and SubAddress part of the argument for the Hyperlinks.Add method. I am guessing the Address part is "" because I am linking to a place in the same workbook. Is that right?
I tried changing the SubAddress to Worksheets("Index") and Worksheets("Index").Range("A1") but that just resulted in an error message.
View 1 Replies
View Related
Mar 19, 2007
I am trying to further automate the process of formatting my financial statements and am hoping someone might get me on the right track here. Currently I create 2 sheets, one called "MMM YYYY" (i.e. month year) and another called "General Ledger".
The process I am currently doing manually is creating a link between each line item on the financial statement and its GL account information on the General Ledger tab.
The description of each line item on the financial statement (the MMM YYYY tab) matches the GL account description on the General Ledger tab with the exception that the GL account description starts with the GL account number and then the description.
For example the "Utilities" line item on the financial statement needs to link to a line on the General Ledger tab that is "7071-45 Utilities".
In my mind I see the macro saying go to the next line and read what it says. Now go to the General Ledger tab and look for something that says the same thing excluding any preceeding numbers and make a hyperlink.
If it helps, I've uploaded a sample of one of the statements as I currently do them with the hyperlinks already set up. There are not any macros in this spreadsheet. If you'd like to take a look at it, you can get it here: ....
View 9 Replies
View Related
Feb 18, 2009
I have a list of file names sans extension in column A. I want to search a folder I specify and if file is found create a hyperlink to said file either in a new cell or in column A.
The code I have does the search fine, but its hyperlinking is offset and I can’t get it to match the link to the file name.
Also it breaks if it can not find the file.
Here is the code...
View 9 Replies
View Related
Sep 11, 2006
I tried to learn VBA about 4years ago, so my knowledge as died. I hope this question has not come up before, i did do a search before. I wish to create a new hyperlink based on a activecell eg. A1=SN0001 then hyperlink will be SN0001. Only thing i can remember how to do is record marco, but ofcourse copying the cell will return a hardcoded value.
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="1"
Where address is :="1" how do i paste the activecell. I have 9000 cells to hyperlink.
View 4 Replies
View Related
Mar 5, 2008
I need to make a Workbook that has hyperlinks in the first sheet that links to a cell in a second sheet and then back again. The only way I have been able to do this is manually create each link in each sheet. I am hoping that this process can be automated as they can not be 'filled' as far as I can see. find attached an example of what I am trying to achieve.
View 2 Replies
View Related
Dec 23, 2013
Creating email drafts with the use of VBA in excel.
I've used some of his code to create an email draft to send a particular range within my excel spreadsheet. The trouble I'm having with it is keeping the hyperlinks within each cell in the range which will take the user to a particular website. How do I keep this formatting when the range is copied into the body of the email.
Example Cell A10 = HYPERLINK("URL","Google")
The hyperlinks are lost. How to keep these? Here is the code
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
Set rng = Range("EmailRange")
[Code] ........
View 8 Replies
View Related
Apr 9, 2014
Can't seem to attach sample Widgets.xls. I need a macro to take the heading (note heading not sheet name) from each worksheet on a workbook (or from a range of nominated worksheets in case I don't want to index the first or last few sheets) and use it as the Description for a Index entry on a nominated sheet e.g. Sheet named 'Index'. In addition that each Description is also a hyperlink back to the sheet with the heading. In this way users can click between each entry on the index to go to the sheet and then click on the heading on the sheet to go back to the index.
Index Sheet
M21, M22 and M22-A BICYCLES1
Q21, Q22 and Q22-A BICYCLES2
R21, R22 and R22-A BICYCLES3
Taking this one step further I ideally want to save this workbook as a pdf and the hyperlinks to remain.
View 4 Replies
View Related
Nov 2, 2009
i'm trying to automatically create hyperlinks in some cells from mail addresses located in another column. this is what came out:
View 2 Replies
View Related
Sep 22, 2007
I've set up a macro to create around 50 http links (sites which i need to monitor every week but whose addresses change slightly every month)
I've also got a macro to open up all these web pages at once so I can look at them in internet explorer.
When these links are nicely created into cells the http link isn't recognised straight away by excel (e.g. doesn't go blue & underlined) until i click in the cell & change or return the cell value - as part of the autocorrect function i think.
Is there a way of getting excel to automatically recognise the http links without having to go individually into every cell.
View 6 Replies
View Related
May 17, 2013
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies
View Related
Apr 29, 2008
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.
So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc
There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button
Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.
FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.
I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.
View 9 Replies
View Related
Feb 21, 2007
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
View 6 Replies
View Related
Jul 21, 2013
I have a range that I named. Let's say that range is called "RangeX". Let's say RangeX is defined by B2:E10
I have a series of formulas that give me the row and column numbers of the starting and ending cell of a subset range I want to create within RangeX. Let's say the starting cell is defined by (1,1) and the ending cell is defined by (3,4) within RangeX. Translated, the starting cell would be B2 and the ending cell would be E4.
How do I create a subset range using the starting cell (1,1) and (3,4) in VBA? I would like to use the row/column reference as RangeX itself could move around.
View 1 Replies
View Related
May 8, 2009
I'm trying to built a range to be used at SUMIF. I have the sheet names at column A:A and want to create something dynamic to avoid selecting sheet by sheet all the neccesary ranges but sumif doesn't recognize a range like A1&"!"&B:B for example. I tried different ways but I can't get it!
View 2 Replies
View Related
Mar 5, 2009
I have a spreadsheet with up to 3000 rows. I have created a macro that subtotals column J and L based on changes in col. A. I now need to create a range beginning with column "A2" thru Column "D2" to last row of data in column a, but offset (-1,0) due to the Grand Total Row. I then have a macro to do a Go To Special to fill the blanks. I am having trouble because each month the number of rows will change.
View 3 Replies
View Related
Mar 16, 2007
How to create a ' range box' on a form that allows you to drag and select a range, like a type 8 InputBox?
View 9 Replies
View Related
Apr 11, 2007
Is it possible in VBA to instance a range without assigning it to a location in a worksheet? I want to use functions like mmult to manipulate "matrices". I don't think that mmult can handle arrays, so I wanted to use ranges. However, I am running lots of iterations, so I would prefer to keep the calculations in memory only.
View 4 Replies
View Related
Feb 26, 2008
This is more acedemic than anything, but I'm trying to create a range that refers to ranges on more than one sheet... is this possible? "Union" doesnt appear to work when the ranges are on different sheets. Netither of the Set My_Range3 statements work below.
Option Explicit
Public Sub test()
Dim My_Range1 As range
Dim My_Range2 As range
Dim My_Range3 As range
Set My_Range1 = Worksheets(1).range("A1:A10")
Set My_Range2 = Worksheets(2).range("B1:B10")
'Set My_Range3 = Union(My_Range1, My_Range2)
'Set My_Range3 = range(My_Range1, My_Range2)
My_Range3.Interior.ColorIndex = 3
End Sub
Both Set My_Range3 statements will work if the ranges are on the same worksheet.
View 4 Replies
View Related
Jan 14, 2014
why I'm getting an error with this code. I have a data set that becomes larger each time I run the report, so by selecting all the cells and naming the range, I don't have to hard-code the cells involved. Then, I want to be able to create a table from that named range. The error comes on the last line of code.
VB:
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Name = "TestRange"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("TestRange"), , xlYes).Name = "Table2"
View 9 Replies
View Related
Feb 20, 2014
I have a macro that using a list of values in a range, checks if there is a corresponding worksheet and if not, use the Template to create one, rename the worksheet and add the value into a cell.
I now have 2 types of template and based upon on the value in the range, I want it to create a worksheet using 'Template 1' for all values < 1000 and 'Template 2' for values > 1000.
Is it possible to amend my code for this?
HTML Code:Â
Sub CreateTimeline()
Dim rng As Range
Set rng = Application.Range("Projects[No.]")
[Code]....
View 2 Replies
View Related
Sep 17, 2009
I want to create ranges in a worksheet that is inactive.I have 3 sheets in the worksheet and i want, while the first sheet remains active, to create ranges in the second one.
I'm using the following code that creates no errors no matter how i use it but it works only if the second sheet is active.
View 10 Replies
View Related
Oct 19, 2009
I have a currently working macro to copy the current sheet as values and create a new workbook in the same folder destination. That code is as follows:
View 10 Replies
View Related
Dec 10, 2009
i created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).
i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on
here is
View 3 Replies
View Related
Feb 3, 2014
.How do you create a popup windows which shows cells on another worksheet that you can change the values based upon a index / match or vlookup?
I select a cell that has a vaule of 102.
then Popups a box which matches that 102 range in another worksheet and shows pulldown (yes no). (Theres about 5 cells (side by side) of yes no pulldowns.
I want the user to then select the values they want and close the popup box.
User selects the following cell with 103. and then goes thru the same process.
View 3 Replies
View Related
