Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How apply one action to all worksheets?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How apply one action to all worksheets?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How apply one action to all worksheets?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How apply one action to all worksheets?
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How apply one action to all worksheets?
Good points, Earl. I agree, it's much easier to update and manipulate data
if it's only in one place! Doug "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
action items- sorting through multiple worksheets by employee name | Excel Discussion (Misc queries) | |||
Why does action on one worksheet affect all worksheets? | Excel Worksheet Functions | |||
Won't apply to all worksheets | Setting up and Configuration of Excel | |||
Same action in different worksheets | Excel Discussion (Misc queries) | |||
Can I protect a range of worksheets in a book with one action? | Excel Worksheet Functions |