Master Sheet Formulas

[CordingBags]

I have a growing workbook, with a MASTER as first sheet.
Each week I copy and rename this master sequentially with the previous sheets in the book.
Thus providing a standardised layout, dates, formulas, formats etc.
Some of the cells in the newly created sheet need to pick up cell references from the previous week.
This I have to do manually. eg sheet 80 cell G23 has the manually entered formula =+'79'!G24.
There are several other cell locations around the sheet that similarly need to reference the preceding sheet.
Whilst this is little more than a chore if I am completing the sheet, it is one more detail to pass on to any deputy.
Is there a formula that could be entered on the MASTER sheet such that when it is copied the new sheet correctly references the preceding.
Many Thanks for any suggestion

 

[Anthony47]

Try the following approach:
Review Master, and link each cell that is expected to be linked to the previous sheet to the same MASTER sheet; examples:

=MASTER!I2

=SUMIF(Master!C2:C16,"abc",Master!D2:D16)

It is possible that, while entering these formulas, you receive the Circular referende error and you will ignore the warning

Then use the following macro to create the new weekly sheet:

Sub NewWeek()
Dim PreSh As String
'
PreSh = Sheets(Sheets.Count).Name
Sheets("Master").Copy after:=Sheets(Sheets.Count)
csh = ActiveSheet.Name
'Application.DisplayAlerts = False
    Cells.Replace What:=csh, Replacement:=PreSh, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
Application.DisplayAlerts = True
End Sub

The macro will assume that the sheet with the highest index (Index refers to the position of the tab with the sheet name) will become the "previous" sheet; then a copy of MASTER will then be set in the last position; then all reference to MASTER will be moved to "previous sheet".
The name of the new sheet will remain the default one, thus MASTER (2). If you wish you may add, just before "End Sub", the instructions that will change automatically the name of the sheet.

Try...

 

[CordingBags]

Hi Anthony
Many thanks for your efforts which I have tried.
I have learnt to change master sheet formulas so they correctly refer when the new sheet is created. eg Master sheet G23 has =+Master!G24.
The new sheet seems to create correctly.
However is there any way to change your macro to create the penultimate lowest? I normally work on the left most sheet, Sheet Index would normally be the penultimate lowest Master is 1, new sheet is 2, such that always working on left most sheet. As the workbook grows the history (lower numbered sheets) disappears off screen to the right.
Sheet creation at the moment refers to the oldest sheet, although the pick ups work "correctly" the new sheet has the oldest data not the current and is placed at the right hand end.
NB Sheets are "named" sequentially, 1,2,3,4, ......... currently on sheet 82. ps lowest numbered sheets are on archived workbooks we only keep about 30 sheets in each book.
Pany Thanks
Paul

 

[CordingBags]

Hi again Anthony
A thought occurs, given the Macro could be called by a key combo, no reason why the Master isn't the highest Index sheet and the new sheet the lowest, so long as the new sheet formulas then refers to the next lowest. No messing with penultimate anything. Could the Macro be changed to this?
Cheers

Paul

 

[Anthony47]

The new version will assume that Sheet(1) is the newest and will copy and modify sheets("Master") as the "new newest":

Sub NewWeekV2()
Dim PreSh As String
'
PreSh = Sheets(1).Name
Sheets("Master").Copy BEFORE:=Sheets(1)
csh = ActiveSheet.Name
'Application.DisplayAlerts = False
    Cells.Replace What:=csh, Replacement:=PreSh, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
Application.DisplayAlerts = True
End Sub

You see the 2 differences, I guess

 

[CordingBags]

Thanks Anthony, works most excellently . Just need to fiddle / find a way for book to correctly rename sheet and it will almost fill itself in

 

[Anthony47]

Just need to fiddle / find a way for book to correctly rename sheet and it will almost fill itself in

You said nothing about how renaming the sheets, but I see that you used a number.
Assuming the "previous sheet" is named as a valid number, then this version will assign the new sheet "number +1"; or a msgbox will remind that the name has to be changed manually.

Sub NewWeekV21()
Dim PreSh As String
'
PreSh = Sheets(1).Name
Sheets("Master").Copy BEFORE:=Sheets(1)
csh = ActiveSheet.Name
'Application.DisplayAlerts = False
    Cells.Replace What:=csh, Replacement:=PreSh, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
Application.DisplayAlerts = True
If IsError(Val(PreSh)) Then
    MsgBox ("Assign manually a new name to this sheet")
Else
    ActiveSheet.Name = Format(Val(PreSh) + 1, "00")
    MsgBox ("New weekly sheet has been created")
End If
End Sub

Another option could be using the Week Number

 

[CordingBags]

Thanks Anthony this is seriously clever, Many Thanks.
Does just the job, creates a new sheet with references to the preceding and renames ready for fresh data. Excellent

 

[Anthony47]

Thank you for the feedback