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

M

MeaclH

Board Regular
Joined
Apr 2, 2014
Messages
112
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1
223-Feb-25FIRSTSUNMONTUEWEDTHUFRISATSUNMONTUEWEDTHUFRISAT
3FORTNIGHT23-Feb24-Feb25-Feb26-Feb27-Feb28-Feb1-Mar2-Mar3-Mar4-Mar5-Mar6-Mar7-Mar8-Mar
4Driver Allocation Officers
5Line 1Rostered DAORAUF. FShiftOFF-DEDO-U1330PB1330CFD1330PL/S1330PBOFFOFF1330BC1330PB1330CFD1330PL/S1330NRNOFF
6Original DAORAUF. FExtension              
7Remarks Remarks              
8Line 2Rostered DAOSALEH. MShift0530NCC0530PL/SOFF-DOFF0530NRN0530BC0530PB0530NCC0530PL/SOFF-DOFF0530CFD0530PL/S0530BC
9Original DAOSALEH. MExtension DECINED EXT            
10Remarks Remarks              
11Line 3Rostered DAORUBERTO. RShiftCDOBONUSOFF-UOFF-UOFF-UCDOCDOCDOPHCPHCEDO-UOFF-U2130PL/S2130BC
12Original DAORUBERTO. RExtension              
13Remarks Remarks              
14Line 4Rostered DAOMEACLEM. HShiftOFF-UOFF-U1330CFD1330PL/S1330NRN1330BC1330PB1330NCC1330PL/SOFF0530NRN0530PBOFF-U1330PB
15Original DAOMEACLEM. HExtension              
16Remarks Remarks              
FN 23FEB-08MAR25
Cell Formulas
RangeFormula
E1:R3E1='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G1
E5:R16E5='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!G35
C5:C16C5='https://metrotrains.sharepoint.com/sites/DriverAllocationTaxiBookings/LIVE Driver Allocation Officer Roster/[DAO Digital Roster.xlsm]FN 23FEB-08MAR25'!C35
Cells with Conditional Formatting
CellConditionCell FormatStop 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:R95Cell Valueending with "?"textNO
E5:R64,E66:R80,E82:R96Cell Valuecontains "AV"textNO
C6,C18,C21,C24,C27,C30,C33,C36,C39,C42,C45,C48,C51,C54,C57,C60,C63,C9,C12,C15Expression=IF(C6=C5,TRUE)textNO
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:R95Cell Valuecontains "OK"textNO
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:R95Cell Valuecontains "DEC"textNO
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:R94Cell Valuecontains "EDO-U"textYES
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:R94Cell Valuecontains "OFF-U"textYES
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:R94Cell Valuecontains "EDO"textYES
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:R94Cell Valuecontains "OFF"textYES
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:R94Expression=OR(COUNTIF(E5,"*A/L*"), COUNTIF(E5,"*BONUS*"), COUNTIF(E5,"*GAZETTE*"), COUNTIF(E5,"*LSL*"), COUNTIF(E5,"*MATLVE*"), COUNTIF(E5,"*PATLVE*"), COUNTIF(E5,"*PHC*"))textYES
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:R94Expression=OR(COUNTIF(E5,"*BLV*"), COUNTIF(E5,"*CDO*"), COUNTIF(E5,"*SDO*"), COUNTIF(E5,"*PDO*"), COUNTIF(E5,"*TFN*"))textYES
 
You must log in or register to reply here.

Similar threads

A
  • Question Question
Automating VBA Border Concise Formula
Replies
4
Views
462
AJLS
A
T
  • Question Question
Compares comma separated strings of information and returns the result if the data is the same
2
Replies
12
Views
251
Peter_SSs
Peter_SSs
V
  • Question Question
Formula issues to find variations when zero is one reference value
Replies
0
Views
261
vladimiratanasiu
V
A
  • Locked
  • Question Question
Peaks and Troughs
2
Replies
10
Views
7K
Peter_SSs
Peter_SSs
A
  • Question Question
Highs and lows
Replies
1
Views
1K
andrewmurray86
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top