Hi,

I did not get any reply to my post of August 17th, therefore I thought it
was easier for you if I enclose the starting VBA code you gave me.

QUOTE
If I understood your description, this worked for me: Specify your two
additional sheets in both arySheets (add to current list) and arySheets1
(only the two added sheets). I have also redefined the arguments to the
function, so you need to replace all you code. Of course do this on a copy
of the workbook until you are satisfied this does what you need.

Option Explicit



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oSheet As Worksheet
Dim ArySheets
Dim arySheets1
Dim bAdded As Boolean
On Error GoTo ws_exit:
ArySheets = Array("Sheet1", "Sheet3", "Added1", "Added2")
arySheets1 = Array("Added1", "Added2")
Application.EnableEvents = False
bAdded = SheetInArray(Sh.Name, arySheets1)
If SheetInArray(Sh.Name, ArySheets) Then
If (Not bAdded) * (Target.Address = "$B$5") Or _
bAdded * (Target.Address = "$A$1") Then
With Target
If .Value = 1 And .Value <= 12 Then
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name < Sh.Name And _
SheetInArray(oSheet.Name, ArySheets) Then
If oSheet.ProtectContents Then
oSheet.Unprotect
If SheetInArray(oSheet.Name, arySheets1) Then
oSheet.Range("A1").Value = .Value
Else
oSheet.Range("B5").Value = .Value
End If
oSheet.Protect
Else
If SheetInArray(oSheet.Name, arySheets1) Then
oSheet.Range("A1").Value = .Value
Else
oSheet.Range("B5").Value = .Value
End If
End If
End If
Next oSheet
Else
MsgBox .Value & " is an invalid value"
.Value = ""
End If
End With
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub

Private Function SheetInArray(Name As String, ArySheets)
Dim fSheet As Boolean
Dim i As Long
fSheet = False
For i = LBound(ArySheets, 1) To UBound(ArySheets, 1)
If LCase(ArySheets(i)) = LCase(Name) Then
fSheet = True
Exit For
End If
Next i
SheetInArray = fSheet
End Function



--
Regards,
Tom Ogilvy
/QUOTE



I summarize the situation as it is today and then try to describe what is
the "extra" feature I'd like to have.
Basically two questions:

I have a folder that contains:

11 WBs (10 referring to different units and 1 consolidating the 10 units)
They all have same format and functions.

Every WB has got many WSs that include a function that allows selecting a
month (1 - 12) in any of the WSs and the result is the YTD at the given
month. (Yr code of August 6th).
This applies for the consolidated WB as well.

In the consolidated WB I have a table that summarizes all the results of the
10 single units (10 WBs).
I have applied the same code to that table, so if I enter 6 I get the
consolidated YTD for June. But of course, in this table I only get the
Totals, because they are in the same WB.

For example:
In my Folder I have all the WBs, where 6 is the month they have been saved
when last closed.

WB unit A €“ Value 3
WB unit B €“ Value 5
WB unit C €“ Value 7
Etc.€¦

If I open them and change to, say 5, then I will get:

WB unit A €“ Value 2
WB unit B €“ Value 3
WB unit C €“ Value 5
Etc.€¦

What happens in the table that summarizes all the units in the Consolidated
WB if I enter month 6, and the single units WBs have been saved and closed in
month 5?

WB Consolidated
WB unit A €“ Value 2
WB unit B €“ Value 3
WB unit C €“ Value 5
Total €“ value 15

So, I will only get the Total to change but the single units remain at the
value theyve been closed and saved in the Folder, that is in this case, 5.
So, what I should need is, when I input the number in the table WS in
Consolidated, is to get the month number to change in the single units as
well.
So, if I enter 6 in the Consolidated WB - Not only I get all the WSs
included into this WB to switch to 6, but also all the 10 single units WBs
switching to 6, so that I can visualize the changes in the consolidated
table that summarizes all the units.
This function should occur regardless if the 10 unit WBs are open or not
(most probably they would be closed).

I was thinking to do this by recording a macro, but I would end up in never
ending macros that open/close all the WBs every time.

-------------------------

Second question:

In the consolidated WBs, in addition to the WSs, there are a good number of
Charts.
When I enter the month number to check the YTD, the charts change accordingly.
When I do presentations, I'd like to see the reference period on the Chart
as well, so that the audience knows that the data shown in the Chart refer to
month 6 or 8 and so on.
Is there a way to "visually" transfer the reference month to the Charts?

I do apologize for being that long, but it is not easy to explain such a
complex question in writing.

Thank you for your help.

Alex