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

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...

Maliki HassaniAsked:
Who is Participating?
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?

Maliki HassaniAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.