Macro To Create Index With Hyperlinks To Worksheet Headers
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
ADVERTISEMENT
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
Oct 30, 2006
I am now trying to get the Index worksheet to extract data from the sheets that it has indexed, for example, it returns the sheet name, but i want it also to return the value that is contained in C2 and C3. Is this possible? I have attached the code which i am using below for ease of reference.
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index...................................
View 2 Replies
View Related
Jul 3, 2014
it doesn't like the fact I have split column headers and I was wondering if there was a way around it or another solution to save me time manually counting or filtering. I can't attach the actual spreadsheet for some reason but attach a screenshot to illustrate what I mean.
I need to count the number of 1's, 2's, 3's etc in each 'On Exit' column for each area. I then need to work out what percentage of the cohort they each are.
View 9 Replies
View Related
Jan 15, 2014
I have a column with rows having strings as below (see sample.xlsx excel attached) .
I want to delimit the string in such a way that the for each parameter, the parameters will beome columns with headers and the value against them will be shown in thier respective column. The output tab in the attached excel might make what I am trying to convey more clear.
So basically CPU should have its own column and its time against it.
There are thousands of such column that we wish to analyse and therefore the ask.
I tried various option to delimit including recording a macro but did not work
CPU: 5,622.871 ms, Sync: 0 ms, Wait: 0 ms, Suspension: 2,399.921 ms
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
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
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
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
Aug 16, 2006
Can an index of all worksheets names be created without using VBA?
View 3 Replies
View Related
Apr 2, 2009
What I’m after is the macro code to remove all the Hyperlinks from the data in the cells in a worksheet.
View 2 Replies
View Related
Nov 21, 2009
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
View 6 Replies
View Related
Jan 20, 2012
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
View 2 Replies
View Related
May 6, 2013
Is there any way in Excel to create single-cell named ranges from a combination of the labels in the rows and column of a spreadsheet.
Here is an example:
CompanyA
CompanyB
CompanyC
CompanyD
Sales
100
200
[code]....
I would like the first cell (containing the 100) to have a defined name of (something like) "SalesCompanyA", and the second cell to be "SalesCompanyB". And so on - e.g. the cell with 300 in it should be "CostCompanyA".
I can do it manually, but I have a huge spreadsheet & was hoping it could be automated. Just to be clear, it needs to be a single cell range. I know you can create a range from a selection - but this seems to create ranges of the entire row and/or column.
View 6 Replies
View Related
Jun 25, 2008
In cells D2:BM75 I have data about grades either in the format 3a, 3b or as an X which denotes not making progress. Across row1 (D1:BM1)there are column identifiers outlining the subject followed (Maths, English etc). Row 2 onwards contains the pupil name. I need a method of identifying which subject they have an X in for each student. Note that there could be blank cells with no grades present.
Example:
.....................Maths....French...History...Geography
Jon Smith........X..........3b.........X...........5b
Bob Brown.......3c.........X..........4b..........X
The output for Jon Smith (in cell BM2) would be Maths, History
The output for Bob Brown (in cell BM3) would be French, Geography
View 3 Replies
View Related
Apr 13, 2006
I need to have a end user form that pulls information from a database and simplifies work for our substitue coordinator. Please be advised I have been working on this and can not get the desired results. I need a form that displays the current date and 5 columns of data the Data needs to be in seperate cells for example cell A1 titled TEACHERS and then the sub coordinator could start typing the name of the teacher in a2 and a predefined database of teachers name, class they teach, etc. MATH, ENGLISH and planning period would be pooled and automaticly fill the cell.
I also would need a second cell say cell B to be call Substitutes and b2- b100 to pull data from a database of substitute names and display their information.
View 9 Replies
View Related
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
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
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
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
Dec 18, 2013
Is it possible to have table headers that are down the side instead of going across the top?
I have searched but have not found an answer so far.
View 1 Replies
View Related
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
Jan 23, 2014
I have uploaded a worksheet that has a macro attached to button 4,
I want the macro to search the header in "All Data" worksheet and transfer the data under them to under the same headers in "Quote" worksheet.
The data in "All Data" could be upto 60 rows of data.
quote test 1.xlsx‎
View 6 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
Feb 26, 2008
I need to create new worksheet with all the rows which has qty (column A) value of 1 and above by clicking on a submit button....
View 9 Replies
View Related
Oct 23, 2008
I need a macro to create the following worksheets and charts from an Excel data set:
Three (3) worksheets (already created manually in attached Excel file):
1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.
2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).
3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)
At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View 14 Replies
View Related
Oct 28, 2009
Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.
View 5 Replies
View Related
Aug 25, 2009
I tried recording a macro to add to a new worksheet, but it gives error while running.
Issue as I understand is, by default excel gives a new worksheet a name (Say Sheet 4), and when u run macro worksheet, new created name may be different.
View 7 Replies
View Related
