VBA generate copy of another sheet, then find and replace links

[MeaclH]

Hi all,
I have a workbook with a sheet for every fortnight of our employees roster. The data is fed from a master roster using the link function.
What I would like to be able to do is auto generate the next fortnights sheet and find and replace the formula for the relevant sheet name from the master.

For example on the sheet titled FN23FEB-08MAR25, the link is ='[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G35
I need to be able to easily create a new sheet using a MsgBox, enter in the next fortnights sheet name, sheet gets created using the input from the MsgBox, and then find and replace all instances of the current link ='[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G35 and change to ='[DAO Digital Roster.xlsm]SHEET NAME INPUT FROM MSGBOX'!G35
The new sheet name will align with the master roster file as the sheet names will all be the same.

Is this doable. Or is there an easier way?

You may be wondering why I have a mirror file. The master file has several groups/teams and the mirror file only shows employees from their specific group/team.

Cheers,

Hayden

DAO Digital Roster (DAO interface).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	23-Feb-25		FIRST		SUN	MON	TUE	WED	THU	FRI	SAT	SUN	MON	TUE	WED	THU	FRI	SAT
3			FORTNIGHT		23-Feb	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar
4	Driver Allocation Officers
5	Line 1	Rostered DAO	RAUF. F	Shift	OFF-D	EDO-U	1330PB	1330CFD	1330PL/S	1330PB	OFF	OFF	1330BC	1330PB	1330CFD	1330PL/S	1330NRN	OFF
6		Original DAO	RAUF. F	Extension
7		Remarks		Remarks
8	Line 2	Rostered DAO	SALEH. M	Shift	0530NCC	0530PL/S	OFF-D	OFF	0530NRN	0530BC	0530PB	0530NCC	0530PL/S	OFF-D	OFF	0530CFD	0530PL/S	0530BC
9		Original DAO	SALEH. M	Extension		DECINED EXT
10		Remarks		Remarks
11	Line 3	Rostered DAO	RUBERTO. R	Shift	CDO	BONUS	OFF-U	OFF-U	OFF-U	CDO	CDO	CDO	PHC	PHC	EDO-U	OFF-U	2130PL/S	2130BC
12		Original DAO	RUBERTO. R	Extension
13		Remarks		Remarks
14	Line 4	Rostered DAO	MEACLEM. H	Shift	OFF-U	OFF-U	1330CFD	1330PL/S	1330NRN	1330BC	1330PB	1330NCC	1330PL/S	OFF	0530NRN	0530PB	OFF-U	1330PB
15		Original DAO	MEACLEM. H	Extension
16		Remarks		Remarks
FN 23FEB-08MAR25

Cell Formulas
Range	Formula
E1:R3	E1	='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G1
E5:R16	E5	='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G35
C5:C16	C5	='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!C35

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	ending with "?"	text	NO
E5:R64,E66:R80,E82:R96	Cell Value	contains "AV"	text	NO
C6,C18,C21,C24,C27,C30,C33,C36,C39,C42,C45,C48,C51,C54,C57,C60,C63,C9,C12,C15	Expression	=IF(C6=C5,TRUE)	text	NO
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	contains "OK"	text	NO
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	contains "DEC"	text	NO
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "EDO-U"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "OFF-U"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "EDO"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "OFF"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Expression	=OR(COUNTIF(E5,"*A/L*"), COUNTIF(E5,"*BONUS*"), COUNTIF(E5,"*GAZETTE*"), COUNTIF(E5,"*LSL*"), COUNTIF(E5,"*MATLVE*"), COUNTIF(E5,"*PATLVE*"), COUNTIF(E5,"*PHC*"))	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Expression	=OR(COUNTIF(E5,"*BLV*"), COUNTIF(E5,"*CDO*"), COUNTIF(E5,"*SDO*"), COUNTIF(E5,"*PDO*"), COUNTIF(E5,"*TFN*"))	text	YES

 

[Sam_D_Ben]

Something like this, This will duplicates the active sheet, assigns a new name provided by the user, and updates all references in the newly created sheet by replacing occurrences of the original sheet name with the new one, Please check

Sub GenerateNextFortnightSheet()
    Dim newWs As Worksheet
    Dim oldSheetName As String, newSheetName As String
    
    oldSheetName = ActiveSheet.Name
    
    newSheetName = InputBox("Enter the name for the new fortnight sheet:", "New Fortnight Sheet")
    If newSheetName = "" Then Exit Sub
    
    ActiveSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set newWs = ActiveSheet
    newWs.Name = newSheetName
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    newWs.Cells.Replace What:=oldSheetName, Replacement:=newSheetName, LookAt:=xlPart, MatchCase:=False
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    MsgBox "New fortnight sheet '" & newSheetName & "' created successfully, with all references updated!", vbInformation
End Sub

 

[MeaclH]

Something like this, This will duplicates the active sheet, assigns a new name provided by the user, and updates all references in the newly created sheet by replacing occurrences of the original sheet name with the new one, Please check

Sub GenerateNextFortnightSheet()
    Dim newWs As Worksheet
    Dim oldSheetName As String, newSheetName As String
   
    oldSheetName = ActiveSheet.Name
   
    newSheetName = InputBox("Enter the name for the new fortnight sheet:", "New Fortnight Sheet")
    If newSheetName = "" Then Exit Sub
   
    ActiveSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set newWs = ActiveSheet
    newWs.Name = newSheetName
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
    newWs.Cells.Replace What:=oldSheetName, Replacement:=newSheetName, LookAt:=xlPart, MatchCase:=False
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
   
    MsgBox "New fortnight sheet '" & newSheetName & "' created successfully, with all references updated!", vbInformation
End Sub

Hi Sam,

Thanks for taking the time! Appreciate it. First part of the code executes as intended, however, the find and replace function doesn't seem to work. The sheet still uses the original sheets reference cells.

I thought it may have been due to sheet protection being on, however, I tested this and it wasn't the case.

Any thoughts?

Cheers,
Hayden

 

[Sam_D_Ben]

This functioning correctly with the provided table. will check

 

[MeaclH]

This functioning correctly with the provided table. will check

Thanks mate.

Here is the file I am working on. I don't think I have changed anything since the first mini sheet.

DAO Roster - Read Only.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1																	Good Friday	Easter Saturday
2	06-Apr-25		UNPUBLISHED	FIRST	SUN	MON	TUE	WED	THU	FRI	SAT	SUN	MON	TUE	WED	THU	FRI	SAT
3				FORTNIGHT	6-Apr	7-Apr	8-Apr	9-Apr	10-Apr	11-Apr	12-Apr	13-Apr	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr
4	Driver Allocation Officers
5	Line 1	Rostered DAO	SALEH. K	Shift	OFF	1330BC	1330PB	1330CFD	1330PL/S	EDO	OFF	0530PL/S	1330BC	OFF	1330CFD	1330PL/S	1330NRN	OFF
6		Original DAO	SALEH. K	Extension
7		Remarks		Remarks
8	Line 2	Rostered DAO	ALLSOPP. R	Shift	0530NCC	0530PL/S	OFF	OFF	0530NRN	0530BC	0530PB	0530NCC	0530PL/S	OFF	OFF	0530CFD	0530PL/S	0530BC
9		Original DAO	ALLSOPP. R	Extension
10		Remarks		Remarks
11	Line 3	Rostered DAO	KANGARAJ. R	Shift	2130PL/S	2130BC	OFF	OFF	OFF	2130PB	2130NCC	2130PL/S	2130BC	2130PB	OFF	0530PL/S	2130PL/S	2130BC
12		Original DAO	FEEGRADO. R	Extension
13		Remarks		Remarks
14	Line 4	Rostered DAO	PERSKE. H	Shift	OFF	OFF	1330CFD	1330PL/S	1330NRN	1330BC	1330PB	1330NCC	1330PL/S	OFF	OFF	1330NRN	1330BC	1330PB
15		Original DAO	RAUF. F	Extension
16		Remarks		Remarks
FN 06APR-19APR25

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C3	Cell Value	beginning with "PUBLISHED"	text	NO
C2:C3	Cell Value	beginning with "UNPUBLISHED"	text	NO
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	ending with "?"	text	NO
E5:R64,E66:R80,E82:R96	Cell Value	contains "AV"	text	NO
C6,C18,C21,C24,C27,C30,C33,C36,C39,C42,C45,C48,C51,C54,C57,C60,C63,C9,C12,C15	Expression	=IF(C6=C5,TRUE)	text	NO
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	contains "OK"	text	NO
E6:R6,E9:R9,E12:R12,E15:R15,E18:R18,E21:R21,E24:R24,E27:R27,E30:R30,E33:R33,E36:R36,E39:R39,E42:R42,E45:R45,E48:R48,E51:R51,E54:R54,E57:R57,E60:R60,E63:R63,E67:R67,E70:R70,E73:R73,E76:R76,E79:R79,E83:R83,E86:R86,E89:R89,E92:R92,E95:R95	Cell Value	contains "DEC"	text	NO
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "EDO-U"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "OFF-U"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "EDO"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Cell Value	contains "OFF"	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Expression	=OR(COUNTIF(E5,"*A/L*"), COUNTIF(E5,"*BONUS*"), COUNTIF(E5,"*GAZETTE*"), COUNTIF(E5,"*LSL*"), COUNTIF(E5,"*MATLVE*"), COUNTIF(E5,"*PATLVE*"), COUNTIF(E5,"*PHC*"))	text	YES
E5:R5,E8:R8,E11:R11,E14:R14,E17:R17,E20:R20,E23:R23,E26:R26,E29:R29,E32:R32,E35:R35,E38:R38,E41:R41,E44:R44,E47:R47,E50:R50,E53:R53,E56:R56,E59:R59,E62:R62,E66:R66,E69:R69,E72:R72,E75:R75,E78:R78,E82:R82,E85:R85,E88:R88,E91:R91,E94:R94	Expression	=OR(COUNTIF(E5,"*BLV*"), COUNTIF(E5,"*CDO*"), COUNTIF(E5,"*SDO*"), COUNTIF(E5,"*PDO*"), COUNTIF(E5,"*TFN*"))	text	YES

 

[MeaclH]

This functioning correctly with the provided table. will check

Mate! my error! I hadn't actually created the new sheet in the master file. Therefore it couldn't reference it. So your solution is perfect!
Cheers