Hyper link problem in different sheets
Hyper link problem in different sheets
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Hyper link problem in different sheets

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have made a sheet on which i have assigned several hyperlink which are linked with the cells present on that sheet only.
    Now i uses a vba code which enable that excel file to create new sheet on the basis of current date. Now when this new sheet is formed the hyperlinks which i have assigned in the source sheet don't appear in the new sheet infact they get connected with the source sheet.
    How i can make excel do this that when a new sheet is created the hyper link also adjust them selves from the previous sheet to the new sheet so that when we click on some hyperlink it take us to the desired cell on the same sheet not on the previous sheet.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might want to try to delete all the hyperlinks in your newly copied worksheet (using the following codes):

    For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
    ActiveSheet.Hyperlinks(i).Delete
    Next i

    And then add the hyperlinks to the worksheet again (using the following codes for each hyperlink):

    ActiveSheet.Hyperlinks.Add Anchor:=Range("E1"), Address:="", SubAddress:=ActiveSheet.Name & "!A1"

    Where "E1" is the where your hyperlink is, and "A1" is where the hyperlink links to.

    HTH

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thankyou for your help but the is it possible that some how each time when a new sheet is created from a master sheet the hyperlinks also adjust themselves for the new sheet as all other formula and headings.


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi webking

    When a hyperlink is created, you have the choice of using a Sheet AND cell reference or only a Cell Reference. It is the latter you want. This way A1 will always be A1 of the sheet the houses the Hyperlink

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sir thankyou but try to see this.

    I have a sheet in which i have created a hyperlink which when click takes the user to cell A3.
    Now when i use the following vba code for excel every new day when the user open a particular excel file a new sheet automatically is created according to the current date.
    Now when this new sheet is created the hyperlink should still take the user on the A3 cell on the new sheet but in my case it takes the user on the previous sheet.
    how i can solve this.

    Option Explicit

    Public Sub auto_open()

    Dim newSheet As Worksheet
    Dim yestSheet As Worksheet
    Dim temp As Date
    Dim tempDate As Date
    On Error Resume Next
    yestSheet = ActiveWorkbook.Worksheets(Format$(Date, "yyyy-mm-dd"))
    If (Err.Number = 0) Then
    Exit Sub
    End If

    tempDate = 0
    On Error Resume Next
    For Each yestSheet In ActiveWorkbook.Worksheets
    temp = CDate(yestSheet.Name)
    If (Err.Number = 0) Then
    If (tempDate < temp) Then
    tempDate = temp
    End If
    End If
    Err.Clear
    Next yestSheet
    On Error GoTo 0
    Set yestSheet = ActiveWorkbook.Worksheets("Draft")
    Set newSheet = ActiveWorkbook.Worksheets.Add(yestSheet)
    yestSheet.Cells.Copy newSheet.Cells
    newSheet.Name = Format$(Date, "yyyy-mm-dd")
    If (tempDate > 0) Then
    Set yestSheet = ActiveWorkbook.Worksheets(Format$(tempDate, "yyyy-mm-dd"))
    yestSheet.Range("E9:E28").Copy production closing balance
    newSheet.Range("B9:B28").PasteSpecial xlPasteValues opening balance of next day in production.
    yestSheet.Range("K9:K28").Copy
    newSheet.Range("H9:H28").PasteSpecial xlPasteValues
    End If
    newSheet.Range("C1").Value = Format(Date, "Short Date")
    newSheet.Range("E1").Value = Format$(Date, "dddd")
    Set newSheet = Nothing
    Set yestSheet = Nothing
    End Sub

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Webking, the problem is not in your VBA code it's the hyperlink itself. Do this.

    Select any cell
    Push Ctrl+K
    Click "Bookmark"
    Type A1 in the "type in cell reference box"
    DO NOT SELECT A SHEET
    Click OK, then OK agian

    Now copy this sheet, or cell or cells to any other sheet. The hperlink will be relative the sheet it resides on.



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-27 02:05 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tommy,
    thanks a lot for helping me so much , do you have some information about vba ? if yes iam trying to solve a problem from last 3 weeks but no use. I have discuss this with so many discussion forum and went through books but did not get any answer.

    Suppose there is excel sheet in a file name "Daily" on which Cell A1, B1, C3 has values input. Now i want to create a link from this sheet to another excel file named "Weekly".
    Now if the source sheet "Daily" remains same then when ever we make entries in the linked cell the "Weekly" Sheet get updated but the problem is sheet in the file "Daily" get changed every day and the "Weekly" Sheet should have some kind of link so that each day it get result from different sheet from the same mentioned cells and get updated. Like on March 21 data from Cell A1, B1, C5 gets into the "Weekly" File Sheet cell A1, B1, C1 , n0w on March 22 data from same excel cell but the sheet will change should go into "Weekly" file sheet in cell A2,B2,C2
    If the above is not clear here some more explaination:-please bear with me on this one..





    Like Suppose the code i mentioned to you creates new sheet according the current date with the same formula and headings and some copy paste that i like from previous sheet which i have used.
    Now all this happen in a single file of excel that on each new day a new sheet automatically comes in act when ever that particular file is opened.

    Now suppose this excel file has a name "DAILY" in which each day a new sheet automatically comes in act and in this new sheet suppose we do entries in CELL A1, C5, C9.

    I want to create a link between this sheet and another file which has a name "WEEKLY" on which the sheet shows the summary of 7 days input or if not 7 shows summary of all the sheets that has been used in "DAILY" file.
    Now this would have been easy if the source sheet is not changed but the problem is each day the source sheet is changed and so the connection of the sheet with the weekly file sheet does not remain.
    Now iam looking for such a vba code like i have for excel so that excel can perform the above daily to weekly function.



  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry for writing tommmy , iam thankful to Dave. and i hope you can help me on the vba problem also

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com