Duplicate sheets with "updated" formula

[Aneurysm]

Good morning everyone,

In the file ABC.xlxs I have 3 sheets. (101;102;103)

Cell A2 sheet 103 shows the average between the values in cell A1 of sheet 101 and cell A1 of sheet 102.

I would like to make it so that when I duplicate (dx move/copy key or ctrl+mouse) sheet 103 (i.e. the last one in the list) creating sheet 104, automatically the average formula in cell A2 of sheet 104 will update taking into account also the value of A1 of sheet 103, and so on to the creation of the next sheets. Basically an average that updates upon duplication of the sheet

Is this possible? If so how?

Thank you all very much

 

[OaklandJim]

I can't promise a solution but I'll try. Describe the process used to "duplicate" a worksheet. Do you always make a copy of the last sheet in the sequence? Do you right click on a tab to make a copy of the selected worksheet?

 

[OaklandJim]

I have code that works like this. Consider that there are three existing sheets. They are named 101, 102, 103. 103 has a formula in cell A2 that calculates the average of cell A1 in 101 and A1 in 102. If you make a copy of sheet 103 that new sheet is automatically named 104 and in cell A2 of sheet 104 is the formula for the average of cell A1 in sheets 101, 102, 103. The question is, how does the value get into cell A1 in the "previous" sheet (which is sheet 103 in the example). Does that sound correct?

 

[simonc833]

I can't promise a solution but I'll try. Describe the process used to "duplicate" a worksheet. Do you always make a copy of the last sheet in the sequence? Do you right click on a tab to make a copy of the selected worksheet?

We have a "master" copy of both sheets and then each week we save a copy of both under a new name (1 is by date,1 is by week number).all the formulas relate to the sheet that change by date if this helps!

 

[OaklandJim]

Will worksheets always be named like in the example: the name of the new worksheet is the latest sheet plus one? Example: existing sheet names are 101 through 105; will and the next sheet be named 106. Are there other worksheets in the workbook?

 

[OaklandJim]

I did not do lots of testing on this so this may not work as expected. If it doesn't let me know. It is the Workbook_NewSheet event in the ThisWorkbook module accessible via the VBA editor. It requires that the file is a Macro-enabled one with the .xlsm file extension or it will not work. So SaveAs your workbook as a Macro-enabled. Google VBA editor for more about how to access the ThisWorkbook module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

'   Used to get the sum of values in cell A1 in worksheets -- excludes the worksheet
'    just added. E.g., Main or Master or Primary.
    Dim dSumValues As Double
    
'   These variables hold the name(s) of worksheets that should
'   not be processed. E.g., Main or Master or Primary.
    Dim sWorksheetToExclude1 As String
    Dim sWorksheetToExclude2 As String
    
'   Count of sheets processed -- excludes the worksheet just added.
    Dim iSheetsCount As Long
    
'   If there are worksheets that should not be processed put their
'   names into these variables. Use a value of nothing  -- "" -- if
'   there is/are not worksheet(s) to exclude.
    sWorksheetToExclude1 = ""
    sWorksheetToExclude2 = ""
    
'   Worksheet object used to iterate through the worksheets collection.
    Dim wsLoop As Worksheet
    
    For Each wsLoop In Worksheets
    
'       If the name of the "loop worksheet" is not the same as the one
'       just added then add the value in the worksheet's cell A1 to sum
'       value and increment the number of worksheets processed.
        If wsLoop.Name <> Sh.Name _
        And wsLoop.Name <> sWorksheetToExclude1 _
        And wsLoop.Name <> sWorksheetToExclude2 _
         Then
            dSumValues = dSumValues + wsLoop.Range("A1").Value
            iSheetsCount = iSheetsCount + 1
        End If
    
    Next wsLoop

'   Put the average of previously existing worksheets into cell A2
'   in the worksheet that was just added.
    Sh.Range("A2").Value = dSumValues / iSheetsCount

End Sub

 

[OaklandJim]

Regarding

'   If there are worksheets that should not be processed put their
'   names into these variables. Use a value of nothing  -- "" -- if
'   there is/are not worksheet(s) to exclude.
    sWorksheetToExclude1 = ""
    sWorksheetToExclude2 = ""

The name of the sheet must be within quote marks like, for example, sWorksheetToExclude1 = "Master"