Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing links to Summary from constantly changing worksheet names
Hi,
After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing links to Summary from constantly changing worksheet names
Just looking at your code...
Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing links to Summary from constantly changing worksheet n
Hi Dave,
Yes, the hyperlinks do work well. But there are still references in the same row as where the user entered the new employee name on the Summary sheet that capture date from specific cells in the newly created worksheet. I can e-mail you the spreadsheet if you wish... I'm amazed at you guys for helping out the Excel global community! I'd buy you guys a beer if you were in Ottawa :-) -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: Just looking at your code... Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Managing links to Summary from constantly changing worksheet n
No thanks to sending me the workbook--I wouldn't open it anyway.
But if you have other hyperlinks in the same row that need to be adjusted, you can look through those cells for hyperlinks--and just delete those hyperlinks and add them back the way you want. But it's difficult to guess what you really mean. ==== A nice thing about using the newsgroups is that there are lots of people who can help. klysell wrote: Hi Dave, Yes, the hyperlinks do work well. But there are still references in the same row as where the user entered the new employee name on the Summary sheet that capture date from specific cells in the newly created worksheet. I can e-mail you the spreadsheet if you wish... I'm amazed at you guys for helping out the Excel global community! I'd buy you guys a beer if you were in Ottawa :-) -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: Just looking at your code... Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Excel wizards know the answer?
Hi Dave,
No these links aren't hyperlinks, they're regular cell references in the same row as where the user typed in a new Employee name, which was subsequently used to create a new worksheet using the inputted employee name via a macro. The problem is that I don't know the worksheet name beforehand as they are created by the user subsequent to me developing this spreadsheet. I realize that this might be a bit too complicated. I wanted users to already have the linked cells set up when they added a new name to the range C10:C408 and then ran the macro to create the new worksheet with this employee name. These worksheets in turn must have cells that link back to the Summary sheet. Any Excel wizards know the answer? -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: No thanks to sending me the workbook--I wouldn't open it anyway. But if you have other hyperlinks in the same row that need to be adjusted, you can look through those cells for hyperlinks--and just delete those hyperlinks and add them back the way you want. But it's difficult to guess what you really mean. ==== A nice thing about using the newsgroups is that there are lots of people who can help. klysell wrote: Hi Dave, Yes, the hyperlinks do work well. But there are still references in the same row as where the user entered the new employee name on the Summary sheet that capture date from specific cells in the newly created worksheet. I can e-mail you the spreadsheet if you wish... I'm amazed at you guys for helping out the Excel global community! I'd buy you guys a beer if you were in Ottawa :-) -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: Just looking at your code... Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Excel wizards know the answer?
You could use a formula that refers to that value in C##.
For instance, this will use the sheet name in C123 and retrieve the value from x44. =IF(ISERROR(CELL("address",INDIRECT("'"&C123&"'!a1 "))),"missing", INDIRECT("'"&C123&"'!x44")) You could change "missing" to "" if you wanted to make the cell look empty. But this is just a guess. You've never shared what the formulas actually look like. klysell wrote: Hi Dave, No these links aren't hyperlinks, they're regular cell references in the same row as where the user typed in a new Employee name, which was subsequently used to create a new worksheet using the inputted employee name via a macro. The problem is that I don't know the worksheet name beforehand as they are created by the user subsequent to me developing this spreadsheet. I realize that this might be a bit too complicated. I wanted users to already have the linked cells set up when they added a new name to the range C10:C408 and then ran the macro to create the new worksheet with this employee name. These worksheets in turn must have cells that link back to the Summary sheet. Any Excel wizards know the answer? -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: No thanks to sending me the workbook--I wouldn't open it anyway. But if you have other hyperlinks in the same row that need to be adjusted, you can look through those cells for hyperlinks--and just delete those hyperlinks and add them back the way you want. But it's difficult to guess what you really mean. ==== A nice thing about using the newsgroups is that there are lots of people who can help. klysell wrote: Hi Dave, Yes, the hyperlinks do work well. But there are still references in the same row as where the user entered the new employee name on the Summary sheet that capture date from specific cells in the newly created worksheet. I can e-mail you the spreadsheet if you wish... I'm amazed at you guys for helping out the Excel global community! I'd buy you guys a beer if you were in Ottawa :-) -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: Just looking at your code... Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Excel wizards know the answer?
Wow! It works!!! Thanks Dave. I owe you one. Cheers, Kent.
-- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: You could use a formula that refers to that value in C##. For instance, this will use the sheet name in C123 and retrieve the value from x44. =IF(ISERROR(CELL("address",INDIRECT("'"&C123&"'!a1 "))),"missing", INDIRECT("'"&C123&"'!x44")) You could change "missing" to "" if you wanted to make the cell look empty. But this is just a guess. You've never shared what the formulas actually look like. klysell wrote: Hi Dave, No these links aren't hyperlinks, they're regular cell references in the same row as where the user typed in a new Employee name, which was subsequently used to create a new worksheet using the inputted employee name via a macro. The problem is that I don't know the worksheet name beforehand as they are created by the user subsequent to me developing this spreadsheet. I realize that this might be a bit too complicated. I wanted users to already have the linked cells set up when they added a new name to the range C10:C408 and then ran the macro to create the new worksheet with this employee name. These worksheets in turn must have cells that link back to the Summary sheet. Any Excel wizards know the answer? -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: No thanks to sending me the workbook--I wouldn't open it anyway. But if you have other hyperlinks in the same row that need to be adjusted, you can look through those cells for hyperlinks--and just delete those hyperlinks and add them back the way you want. But it's difficult to guess what you really mean. ==== A nice thing about using the newsgroups is that there are lots of people who can help. klysell wrote: Hi Dave, Yes, the hyperlinks do work well. But there are still references in the same row as where the user entered the new employee name on the Summary sheet that capture date from specific cells in the newly created worksheet. I can e-mail you the spreadsheet if you wish... I'm amazed at you guys for helping out the Excel global community! I'd buy you guys a beer if you were in Ottawa :-) -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 "Dave Peterson" wrote: Just looking at your code... Doesn't this line do what you want? cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value It adds the hyperlink to the cell in column C and points to the new worksheet (cell A1)? What am I missing? klysell wrote: Hi, After developing a macro to create worksheets based on inputted employee names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update links to these constantly changing 200+ spreadsheets (6th sheet and on). These links are contained on the same row where the user has entered in a new employee name. Since the name of the new worksheet is not known at time of development of spreadsheet, the links (e.g. on the 196th row) should refer to the new spreadsheet (e.g. entered in C196). Any help would be immensely appreciated! Thanks in advance, Kent Lysell P.S. Here is my code that propagates the names in C10:C408 to their respective worsheets and creates hyperlinks to each of the newly generated sheets: Private Sub CommandButton1_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet ActiveSheet.Unprotect Password:="1111" Set ws = ActiveSheet Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp) Set Rng = ws.Range("c10", LastCell) For Each cell In Rng If Not IsEmpty(cell) Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then Sheets("Master").Visible = True Sheets("Master").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = cell.Value cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value ActiveSheet.Protect Password:="1111" Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="1111" Next ws End Sub -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary of Worksheet names | Excel Discussion (Misc queries) | |||
Protecting/managing links from my worksheet | Excel Discussion (Misc queries) | |||
Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
Linking to Workbooks with Constantly Changing Names | Excel Discussion (Misc queries) | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) |