Automatic updating of Hyperlink depending upon date value in cell

[Lazz0]

I have a workbook that contains 84 sheets,

In Four of those sheets have a cell ("E1") that has a value based upon the formula =Now() to provide the value with ="S"&TEXT(E1,"mmmYY"); creating the names for use in naming the individual sheets , SJan22 through to SDec22 depending upon the month of the year.

I have another Introductory page that has 12 Hyperlinks to access 12 individual sheets with the Tab names SJan22 through to SDec22

This all works very amicably until the year Changes to 2023 and then all of the hyperlinks have to be recreated and changed to SJan23 through to SDec23.

Is there some way to automatically update the hyperlinks to change from SJan22 ........................................SDec22 through to SJan23.....................................Sdec23.

I have the Macro to automatically change the Sheet Tab name from SJan22 to SJan23 but I am having trouble automating the Hyperlink Change.

Can someone please shed some light on the problem

Regards Lazz0

 

[davesexcel]

Would find/replace not work? Practice with it.
Select the range, hit "ctrl & H", search for 22 and replace with 23 hit replace or replace all button

 

[Lazz0]

Would find/replace not work? Practice with it.
Select the range, hit "ctrl & H", search for 22 and replace with 23 hit replace or replace all button

Thanks Dave,
I am creating this programme for people who are not very computer literate and I need for this to happen automatically without them having to be involved or for them to be wanting me to be available 24/7

 

[davesexcel]

I believe the macro recorder can work with find&replace. If you try that, post the code and you could get help cleaning the code up.

 

[Lazz0]

Good Evening Dave,

Just to let you know, I have sorted out my problem by using a combination of Macro's to select the individual sheet that I need and general Hyperlink on the selected Sheet / Sheets to move back to the Menu Page that has all of the Macro's to select individual sheets.

I used the Macro's below to access the sheets and they were in stored in "This Workbook" for general access by all sheets.

Sub HYPERLINKTOOCTOBER()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets(WS1.Range("L1").Value)
' Figure out which row is the next row
Worksheets(WS1.Range("L1").Value).Activate
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'WS2.Cells(NextRow, 1).Resize(1, 1).Value = Array(WS1.Range("N19"))
End Sub

Sub hyperlinkTojULY()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets(WS1.Range("M1").Value)
Worksheets(WS1.Range("M1").Value).Activate
' Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
End Sub
Sub hyperlinkTojanuary()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets(WS1.Range("K1").Value)
Worksheets(WS1.Range("K1").Value).Activate
' Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
End Sub

I do not know if they are the correct Macros to use or if there is another way but they work for me and I am happy with the result.

Thank you for your information and interest in my problem.

Regards
Lazz0