How apply one action to all worksheets?
Doria,
This works in XL2k. I seem to remember that FillAcrossSheets can be buggy,
but not sure. Paste this in the ThisWorkbook module in the Visual Basic
Editor. Replace "Sheet1", etc. with all of the Sheet names that you want
this to apply to. If you have a changing number of sheets, post back and
somebody will give you the code to fill the array at run time:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sheets_array As Variant
On Error GoTo err_handler
Application.EnableEvents = False
If Not Intersect(Target, ActiveSheet.Range("B4")) Is Nothing Then
sheets_array = Array("Sheet1", "Sheet2", "Sheet3")
Sheets(sheets_array).FillAcrossSheets ActiveSheet.Range("B4")
End If
err_handler:
Application.EnableEvents = True
End Sub
hth,
Doug Glancy
"Doria/Warris" wrote in message
...
Hi,
Yes, maybe I wasn't clear enough.
All the sheets have already the possibility to select the month in B4,
what
I want is to trigger the action when I put the number in any of the sheets
so that all of them automatically switch to that number.
If I enter 6 in sheet 1 I expect that also in sheet 12 the same action has
taken place.
I repeat, the functionality to get the YTD is already working in every
sheet
individually, but as it is now, if I enter 6 in sheet 1, sheet 12 and also
all the others remain to their previous month, 5.
Hope is now clearer.
Thanks
Alex
"ijb" wrote in message
...
If you select multiple sheets (either ctl-clicking each sheet or
shift-clicking a range of sheets) and then enter a value into one of
them
it
should replicate over all the sheets.
--
If I've mis-understood the question please tell me.
HTH
ijb
Remove nospam from my e-mail address to talk direct
Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help
"Doria/Warris" wrote in message
...
Hi,
I finally managed to create an arcaic mechanism that enables me to
input
a
number from1 to 12 (months) in a specific cell and get the YTD
results.
I have now another, hopefully, simpler issue to solve.
The worksheets have the same format, the cell is B4 in all of them,
how
do
I
get the number I input to appear on all the sheets contemporaneously?
For instance, I enter 6 in sheet1 to see the June YTD results, what
I'd
like
to see is also sheet2, sheet3 etc, to switch to 6, regardless from
which
sheet the input occurs.
I hope the question is clear enough
Thank you
Alex
|