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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 42

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 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