Excel: Code Modification to Count/Average to pull values in all sheets

Posted on 2011-02-22
Last Modified: 2012-05-11
Greetings Experts!

This question refers to a previous post that delt with Count/Average. The question was resolved by adding to the spreadsheet the following:

Sub UpdateCntAvgTable
Modification to Sub UpdateChart_ArrayOnLBLostFocus
Added named range cPlotCntAvg
Added named range cPlotRange
Sheet "Executive Rollup Data"  JG:KN we added a range that would populate values based on what is selected in the listbox.
I have duplicated this process (JG:KN ) to the rest of the sheets, like instructed.  I am at the point where I need a way to have it populate the rest of the sheets and not just have the code looking at only the Executive Rollup Data sheet.

Currently, it is grabbing only what ID's are listed in Executive.  
Need assistance on what options are out there to get this do be individually called.
Thoughts are that we need to create named ranges for all sheets like cPlotCntAvg and cPlotRange, but the VBA code still need to have a case statement to tell it to look at the name of the sheet to run a specific version of the UpdateCntAvgTable.

Please see attached file...

Question by:Maliki Hassani
LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 34958208
Ok.  I didn't fix it all, so you'd have a chance to go through it and add to the other executive report pages.

I assumed Summary Data and Executive Rollup count/average reports were identical (re: pointed to the same data sheet).  I updated the code for Executive Rollup/Summary and also for Surveillance.  Hopefully, your review of this will enable updates for the other tabs.  Otherwise, just holler.

Ok - in the code there was some documentation about what could change to support multiple versions of this.

This was done in 4 steps:

1 - Deleted the globally scoped named ranges:  cPlotCntAvg, cPlotRange, and rPlottable.
2 - Created a local-scoped name for each of these ranges in the Executive Rollup Data sheet and the Surveillance sheet.  The ranges point to the same areas of the sheet, but the names are now LOCALLY scoped.  E.g., the same named range can exist on different sheets and when SHEET is ahead of the named range - e.g., 'Executive Rollup Data'!cPlotCntAvg, that means its pointing to THAT sheet, THAT range.  When you're done, you'll have as many cPlotCntAvg named ranges as there are sheets where you want this to happen - ALL must be locally scoped.  So when the name is created, instead of scope: Workbook, you need to pick the sheet you're on (and if you make a mistake, it won't work properly) - in the name manger, you can see the list of names and SCOPE - so just check them as you go.
3 - Went to the ListBox3 activeX control and set the fill range to 'Sheet'!rPlotTable - example 'Executive Rollup Data'!rPlotTable for Executive Rollup and Summary, and 'Surveillance'!rPlotTable for Surveillance

4 - Then, I modified the two routines, as below, to account for the precedent datasheet name ahead of the named range.  See lines 19, 21, 37 & 41 and the use of the variable 'datasheet'.  These were the code modifications.
Sub UpdateChart_ArrayOnLBLostFocus(lBox As Variant, dataRange As String, datasheet As String, chartSheet As String, chartID As String, obUsed As Boolean)
Dim lItem As Long
Dim lbItemCount As Integer
Dim myDateRng As Integer

    Application.EnableCancelKey = xlDisabled
    lbItemCount = 0
    If lBox.ListCount <> 0 Then 'only if something is selected, otherwise, do nothing
        For lItem = 0 To lBox.ListCount - 1 'interrogate the listbox

            If lBox.Selected(lItem) = True Then
                ReDim Preserve lbArray(lItem + 1) 'dynamically adds an item to the array
                lbArray(lbItemCount) = lBox.List(lItem) 'populates our array
                lbItemCount = lbItemCount + 1
            End If
        Next lItem
        Call UpdateGenericChartArray(lbItemCount, dataRange, datasheet, chartSheet, chartID, obUsed)
        myDateRng = Sheets(datasheet).Cells(2, Range(dataRange).Columns.Count - obMainChart).Value 'get the date range - if this will be in every summary then this could be dataSheet instead of "Executive Rollup Data"

        Range("'" & datasheet & "'!cPlotRange").Value = myDateRng

        Call UpdateCntAvgTable(lbItemCount, datasheet)
        'now fix listbox back, just in case
        For lItem = 0 To lBox.ListCount - 1
            lBox.Selected(lItem) = False
            For j = 0 To lbItemCount - 1
                If lBox.List(lItem) = lbArray(j) Then
                    lBox.Selected(lItem) = True
                End If
            Next j
        Next lItem
    End If

End Sub
Sub UpdateCntAvgTable(lbItemCount As Integer, datasheet As String)
Dim rPlot As Range
Dim lItem As Long

    Set rPlot = Range("'" & datasheet & "'!cPlotCntAvg")
    rPlot.ClearContents 'clear what was last plotted
    For lItem = 0 To lbItemCount - 1 'then rebuild the range
        rPlot.Cells(lItem + 1, 1).Value = lbArray(lItem)
    Next lItem

End Sub

Open in new window

That's it.  PS if your listbox control doesn't scroll, you have to re-create it.  I did that for one, but left the others alone.  They are quirky, yes?


Author Comment

by:Maliki Hassani
ID: 34961392
Excellent!  Almost done with it!  Great stuff..  I didn't even know about locally naming ranges, just workbook.  Like the way you have it LOOKING for the sheet name to fire!!  Thanks..  Closing

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now