Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to sheet with name in cell
I have a Maco to paste a persons name in a new row on a master sheet from a
start sheet. The persons name is now in A1 on master sheet. next time the name will be in A2 and so on. Before a new name is added to the master, a new sheet is created with the same name as the persons name. I cant figure out how to automaticaly make a hyperlink after the new name is added. I would like to click the persons name in the master sheet in A2, A3 so on to open that persons sheet. Is there a macro I could add to my program after it paste new name in Master sheet that would do this? Thanks for the help. Here is what I got. Range("C2").Select Selection.Copy Sheets("YearSummery").Select With ActiveSheet nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & nextrow).Select ActiveSheet.Paste Application.CutCopyMode = False ******I THINK THIS IS WHERE I NEED SOMETHING******* ActiveCell.Offset(1, 0).Activate ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) End With ' End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to sheet with name in cell
To place the name in the source sheet onto the next available row on the
destination sheet, withOUT selections. Notice the dots . in the with with sheets("yearsummery") lastrow=.cells(rows.count,"a").end(xlup).row+1 range("c2").copy .range("a" & lastrow) end with Now for the next problem. Do not use hyperlinks. right click sheet tabview codeinsert thisdoubleclick on the sheet name to goto the sheet Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then msgbox "not there" 'GetWorkbook ' calls another macro to do that Else Application.Goto Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub Range("C2").Select Selection.Copy Sheets("YearSummery").Select With ActiveSheet nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & nextrow).Select ActiveSheet.Paste Application.CutCopyMode = False ******I THINK THIS IS WHERE I NEED SOMETHING******* ActiveCell.Offset(1, 0).Activate ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) End With ' -- Don Guillett Microsoft MVP Excel SalesAid Software "BC" wrote in message ... I have a Maco to paste a persons name in a new row on a master sheet from a start sheet. The persons name is now in A1 on master sheet. next time the name will be in A2 and so on. Before a new name is added to the master, a new sheet is created with the same name as the persons name. I cant figure out how to automaticaly make a hyperlink after the new name is added. I would like to click the persons name in the master sheet in A2, A3 so on to open that persons sheet. Is there a macro I could add to my program after it paste new name in Master sheet that would do this? Thanks for the help. Here is what I got. Range("C2").Select Selection.Copy Sheets("YearSummery").Select With ActiveSheet nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & nextrow).Select ActiveSheet.Paste Application.CutCopyMode = False ******I THINK THIS IS WHERE I NEED SOMETHING******* ActiveCell.Offset(1, 0).Activate ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) End With ' End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hyperlink to sheet with name in cell
Hi Don, Thanks for the reply.
This is new to me and your reply confused me. I think the problem might be me explaining it. Would it be posible to send you the file so you can look at it when you have time? Then you can see what im looking at. Thanks Again BC "Don Guillett" wrote: To place the name in the source sheet onto the next available row on the destination sheet, withOUT selections. Notice the dots . in the with with sheets("yearsummery") lastrow=.cells(rows.count,"a").end(xlup).row+1 range("c2").copy .range("a" & lastrow) end with Now for the next problem. Do not use hyperlinks. right click sheet tabview codeinsert thisdoubleclick on the sheet name to goto the sheet Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then msgbox "not there" 'GetWorkbook ' calls another macro to do that Else Application.Goto Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub Range("C2").Select Selection.Copy Sheets("YearSummery").Select With ActiveSheet nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & nextrow).Select ActiveSheet.Paste Application.CutCopyMode = False ******I THINK THIS IS WHERE I NEED SOMETHING******* ActiveCell.Offset(1, 0).Activate ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) End With ' -- Don Guillett Microsoft MVP Excel SalesAid Software "BC" wrote in message ... I have a Maco to paste a persons name in a new row on a master sheet from a start sheet. The persons name is now in A1 on master sheet. next time the name will be in A2 and so on. Before a new name is added to the master, a new sheet is created with the same name as the persons name. I cant figure out how to automaticaly make a hyperlink after the new name is added. I would like to click the persons name in the master sheet in A2, A3 so on to open that persons sheet. Is there a macro I could add to my program after it paste new name in Master sheet that would do this? Thanks for the help. Here is what I got. Range("C2").Select Selection.Copy Sheets("YearSummery").Select With ActiveSheet nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & nextrow).Select ActiveSheet.Paste Application.CutCopyMode = False ******I THINK THIS IS WHERE I NEED SOMETHING******* ActiveCell.Offset(1, 0).Activate ActiveCell.EntireRow.Insert Shift:=xlDown ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) End With ' End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Hyperlink to cell in different sheet | Excel Discussion (Misc queries) | |||
hyperlink to sheet name where sheet names=cell content | Excel Programming | |||
Creating a hyperlink to a cell on a different sheet | Excel Worksheet Functions | |||
look for sheet name in list and creat hyperlink to cell | Excel Programming | |||
look for sheet name in list and creat hyperlink to cell | Excel Programming |