Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
continuing: Incorporating error handling to Bob's code
Hi Bob, Tom, and Jim:
When I incorporated the macro which disseminated the list of names on "Summary" sheet in C9:C408 to worksheets, I came across another problem: the links corresponding to the worksheet names in C9:408. Each worksheet that is generated by the maco has cells that link back to the Summary sheet adjacent to the employee name (in C9:C408). For example, there would be salary data in E41 of that macro-generated worksheet (EmployeeName1) that would have to link back to the Summary sheet in the same row as EmployeeName1. By design, all newly inputted employee names in Column C on Summary sheet, that would be propagated to new worksheets via the macro, would have the proper links from this new worksheet back to the Summary Sheet in the same row as the inputted employee name. Here is my macro thus far (which ideally would establish the appropriate cell link back to the Summary sheet): 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 ActiveSheet.Protect Password:="1111" cell.Hyperlinks.Add Anchor:=cell, _ Address:="", _ SubAddress:="'" & ActiveSheet.Name & "'!A1", _ TextToDisplay:=cell.Value Sheets("Master").Visible = False End If End If Next Application.Goto Reference:="Summary" ActiveSheet.Protect Password:="1111" End Sub Any help would be greatly appreciated! Thanks in advance, Kent Lysell -- Kent Lysell Financial Consultant Ottawa, Ontario (613) 907-1211 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorporating error handling to Bob's code | Excel Programming | |||
Error handling in this code | Excel Discussion (Misc queries) | |||
VB handling on mdi print error code 1004 | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
continuing code on another line? | Excel Programming |