How apply one action to all worksheets?

D

Doria/Warris

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
 
I

ijb

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
 
D

Doria/Warris

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
 
D

Doug Glancy

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
 
E

Earl Kiosterud

Doria,

Another, and non-macro, solution would be to put the month in some common
sheet containing a named cell, like "Month". Then in the various month
sheets, refer to that name, =Month. You'd want to protect that cell to keep
users from typing into it.

Having similar data in separate sheets precludes the use of a lot of
terrific Excel functionality. I'll include my standard blurb on the
subject:

There's a lot of Excel functionality that isn't available when similar data
is spread across multiple sheets, as well as across workbooks. Questions
abound where users already have data in separate sheets, and now want to
find certain data, summarize the data, etc. and there are no direct means to
do that.

If the layout of the data in the sheets will be the same (same column
headings), it is generally best to put all the data in a single sheet, with
an additional column for what originally was the various sheets. For
example, if you have a sheet for each month, put all the data in a single
sheet, with an additional column for month. An Autofilter can easily
reduce this consolidated sheet to the equivalent of one of the original
(month) sheets. Now you can sort in various useful ways, use Data -
Subtotals, easily make a pivot table to summarize the data, use database
functions (DSUM, COUNTIF, etc.).

If the separate sheets already exist, it's a straightforward one-time
project to combine them. Just make a sheet with the extra (month) column.
Now paste the records from the first sheet, and enter Jan into the month
column and copy down with the fill handle or copy/paste. Repeat for the
other sheets.
 
D

Doug Glancy

Good points, Earl. I agree, it's much easier to update and manipulate data
if it's only in one place!

Doug
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top