How do I copy a formula to grouped sheets, and then later force the ungrouped sh

Question

How do I copy a formula to grouped sheets, and then later force the ungrouped sheets to re-evaluate the formula again?

Hi all,

I am having a dreadful time with grouping and applying a formula. Let me explain;.

As shown below in the screenshot, I have created a very simple VBA formula in a module calleed 'UserDefinedFunctions'. All it does is return the sheet number -1 ((to reflect the day after the start date)). The formula works correctly and returns the correct value (eg. When I call it on Sheet 1, it returns 1, on Sheet 2, it returns 2, and so on.).

Here is the problem:

I have 100 sheets and I need to insert the following formula in cell $C$5 on all the pages:

=TEXT(B4L_START_DT+SheetNumSinceStart(),"ddd, mmm dd, yyyy")

So to do this, I select/group all the sheets in my workgroup (grouping) -> return back to the first tab -> and enter the above formula into cell $C$5. I then ungroup all the sheets.

The formula is correctly copied to cell $C$5 on each sheet, but, the formula is not evaluated. So, as shown below, each of 84 tabs, have exactly the same date. What should happen is each sheet should have an incremental date. So for example in the screenshot below, all these sheets have

Function SheetNumSinceStart() As Integer ' This function will return the sheet/page index, which refers to the respective 'day' of the B4L program. ' It is set to be -1 to be mathematically accurate. This is account for the fencepost error. ' Eg. The 2nd day of the B4L program is one day after the start day. This function accounts for that. Dim SheetDayNumber As Integer SheetDayNumber = ActiveSheet.Index SheetNumSinceStart = SheetDayNumber - 1 End Function

Explanation / Answer

Hi

Try replacing your function code with the following version:;

'=========>>
Option Explicit

'--------->>
Public Function SheetNumSinceStart() As Integer
Dim DaySheetNum As Integer
Dim sStr As String
Dim SH As Worksheet

sStr = Application.Caller.Parent.Name
Set SH = ActiveWorkbook.Sheets(sStr)
DaySheetNum = SH.Index
SheetNumSinceStart = DaySheetNum - 1
End Functionn!

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.