I would like to summarize all "yes" respose contained in 15 tabs. See the attached.
I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
Last edited by korr77; 02-03-2010 at 11:37 AM. Reason: finalize posting
1) Right-click on the SUMMARY TAB and select VIEW CODE
2) Paste in this sheet activation macro:
3) Close the editor and save your workbook as a macro-enabled workbook
Please Login or Register to view this content.
Now each time you bring up the Summary sheet, it will offer to update.
Remove the line of code in red if you want the update to just occur and not ask you.
Last edited by JBeaucaire; 02-02-2010 at 04:19 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
korr77,
See the attached workbook "Workbook_SheetChange Event J3_J27 - copy data to Summary Tab - KC Environmental Aspects Impacts questions - korr77 - SDG10.xlsm" with the Workbook_SheetChange Event that is triggered by a change in range J3:J27 for all the worksheets except "Process Input Tab" and "Summary Tab".
If the change is "Yes", then the correct information is copied to the next row in the "Summary Tab" worksheet.
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
I get a runtime error "1004" can't change part of a merged cell. When I debug it highlights this line in yellow
Range("A2", Range("D2").End(xlDown)).Clear 'clear existing summary
There were no merged cells on the Summary tab, so that error is unexpected...
Can you repost your workbook with the macro installed so I can see that error in action?
this works great..can it look for "yes" in the significant column versus the "objective and targets" column? basically look for all yes responses in column "I", summarize those results. In some cases I will have significant aspects with "no" for the objective and target.
here it is...
Looks like I'm to late again!
On your summary tab, why did you put a merged cell in row 18? Delete that.
Also, you should set at least ONE row on one of the other sheets to YES so that it will transfer. The sample you just uploaded has no values set to YES in their column J.
Ah, I think I got the FILTER column wrong. Change to this:
Please Login or Register to view this content.
Remove the line of code in blue to make it occur automatically.
great...thanks...I really want yes results from column I..how do I change to code to look there instead?
Awesome...thanks so much, it appears to work perfectly.
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
One other thing...how can I clear all results on the summary tab each time its click. Currently, the macro runs each time you click the tab and inserts several blank rows. So if you click away and back again new blank rows are inserted above the returned data.
That shouldn't happen based on the sample sheet you provided. The macro is designed to clear from row2 down to the bottom of the data set each time it activates, but if you've got blanks in your data set I can envision an issue cropping up there.
Change this:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks