Master Sheet Formulas

CordingBags

New Member
Joined
Mar 7, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Excel Formula:
=MASTER!I2
Excel Formula:
=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:
VBA Code:
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

New Member
Joined
Mar 7, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
3,244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The new version will assume that Sheet(1) is the newest and will copy and modify sheets("Master") as the "new newest":
VBA Code:
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

New Member
Joined
Mar 7, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Thanks Anthony, works most excellently (y). Just need to fiddle / find a way for book to correctly rename sheet and it will almost fill itself in :):)
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

CordingBags

New Member
Joined
Mar 7, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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 :)(y):)(y)
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thank you for the feedback
 

Forum statistics

Threads
1,181,708
Messages
5,931,592
Members
436,795
Latest member
mazigazi

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
Top