Hyper link problem in different sheets

[webking]

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.

[BabyTiger]

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

[webking]

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.

[Dave Hawley]

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

[webking]

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

[Dave Hawley]

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 ]

[webking]

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.

[webking]

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