How to updated pivot tables on different tabs from user form parameters?

[dovermac]

Here is what I am talking about. This superintendent will periodically request a data file and this is exactly how they will get it.

https://www.dropbox.com/s/r0dzbfkr05...eb2501c8cc.csv

Is there a way to automate it as it's put into the workbook with 5 char columns for those code columns?

[dovermac]

An update. I injected all your code. When I select an ISD, District, and School Bldg (doesn't matter which combination), I get a run-time error 1004: ## is not an item of this field. The line below in red is always the culprit.

Public Function FilterPivot( _
ByVal PivotTable As String, _
ByVal ISDCode As String, _
ByVal DistrictCode As String, _
ByVal BuildingCode As String) As Boolean
Dim pvtItem As PivotItem

Application.ScreenUpdating = False

With Worksheets("Working Pivot Tables").PivotTables(PivotTable)

.PivotFields("ISDCode").EnableMultiplePageItems = False
.PivotFields("DistrictCode").EnableMultiplePageItems = False
.PivotFields("BuildingCode").EnableMultiplePageItems = False

With .PageRange

.Cells(1, 2).Value = ISDCode
.Cells(2, 2).Value = DistrictCode
.Cells(3, 2).Value = BuildingCode

[Bob Phillips]

What I did was took all 5 years, d/l the CSV files, formatted those 3 columns as "custom" and then used my Data Acquisition form (aka CallUserForm2) to upload them in order to the combined tab so they looked correct. My project leader noted that the pivots are not reflecting the combined codes as 5 chars. I think that's the because the data was originally CSV and in many cases only 1 character, not 5. If you want, I could throw one original CSV file so you can see what I'm talking about. I'm just wondering is it possible to go from, for example, a 1 character column under CSV, to a 5 character column that gets "copied" around without losing its formatting? I'm pretty sure my way won't work for an executive.

I'm still not getting the problem. As I understand, Working Pivot tables is just a staging between the Combined worksheet and the Summary worksheet and charts. Your executive shouldn't care a monkey's what Combined and Working Pivot Tables look like (I would even hide them), only Summary. And on Summary, District and Building shows as 5 chars, ISD can be formatted same.

[Bob Phillips]

An update. I injected all your code. When I select an ISD, District, and School Bldg (doesn't matter which combination), I get a run-time error 1004: ## is not an item of this field. The line below in red is always the culprit.

Public Function FilterPivot( _
ByVal PivotTable As String, _
ByVal ISDCode As String, _
ByVal DistrictCode As String, _
ByVal BuildingCode As String) As Boolean
Dim pvtItem As PivotItem

Application.ScreenUpdating = False

With Worksheets("Working Pivot Tables").PivotTables(PivotTable)

.PivotFields("ISDCode").EnableMultiplePageItems = False
.PivotFields("DistrictCode").EnableMultiplePageItems = False
.PivotFields("BuildingCode").EnableMultiplePageItems = False

With .PageRange

.Cells(1, 2).Value = ISDCode
.Cells(2, 2).Value = DistrictCode
.Cells(3, 2).Value = BuildingCode

I was testing with Oakland Schools/Oakland Schools or Birmingham/any building or no building and it all worked fine. What values are you using, I will try those on mine see if it makes a difference. As a last resort, I can post my workbook somewhere.

[dovermac]

I tried Oakland Schools, etc like you and got the same thing. I did mention to my project leader your recommendations and he replied with -
"We don't want to deslect the blank from the pivot tables because that is ensuring that we are working with the district summary record and not the building records which is what we want in this case.

On the EnrollmentByGrade pivot table the requirements are to show only the current school year in the graph so the school year is required."

Not sure if that matters or not. As for the working pivot thing, I don't know if the exec will use that or not, but for now, let's skip it. Did you catch my last post about somehow automating the input of CSV files into this workbook and converting those 3 columns to 5 characters? I can't have the superintendent manually formatting these columns every time. You know that won't fly ;-)
P.S. Almost forgot, is there a way to change the display name on the summary page to match what was chosen? I didn't code that, but was curious.

[Bob Phillips]

I tried Oakland Schools, etc like you and got the same thing. I did mention to my project leader your recommendations and he replied with -
"We don't want to deslect the blank from the pivot tables because that is ensuring that we are working with the district summary record and not the building records which is what we want in this case.

That is not what I meant. What I was saying is that the charts are showing blank entries, which have no values. They are irrelevant, create a datapoint on the series, which obscures a little. Best to just filter it in the pivots.

On the EnrollmentByGrade pivot table the requirements are to show only the current school year in the graph so the school year is required."

Okay, but how does the current school year get selected, it isn't on the selection form?

Did you catch my last post about somehow automating the input of CSV files into this workbook and converting those 3 columns to 5 characters? I can't have the superintendent manually formatting these columns every time. You know that won't fly ;-)

I did respond to that in a separate thread as well, but I think I am still missing it.

Almost forgot, is there a way to change the display name on the summary page to match what was chosen? I didn't code that, but was curious.

Which display name are you referring to?

[dovermac]

That is not what I meant. What I was saying is that the charts are showing blank entries, which have no values. They are irrelevant, create a datapoint on the series, which obscures a little. Best to just filter it in the pivots.

I'll forward this to the PM.

Okay, but how does the current school year get selected, it isn't on the selection form?

I'll run this by my PM. Not my call.

I did respond to that in a separate thread as well, but I think I am still missing it.

This kind of lost me. Is it still confusing? I'm sure I'm probably explaining it poorly. Did you take a look at the source CSV file from the link I sent? My way of formatting the 3 columns and putting it into the workbook is inefficient I know.

Which display name are you referring to?

The title at the top of the Summary tab in the black bar. Or does your workbook already accurately display the name of whatever you picked in the PS form like Oakland Schools, etc?[/QUOTE]

[Bob Phillips]

I did respond to that in a separate thread as well, but I think I am still missing it.

This kind of lost me. Is it still confusing? I'm sure I'm probably explaining it poorly. Did you take a look at the source CSV file from the link I sent? My way of formatting the 3 columns and putting it into the workbook is inefficient I know.

I did look, but it doesn't help me I feel. I understand how the data arrives, I understand how it gets changed when you post to Combined, but I don't understand where it is showing as a problem. As I say, Summary only has a short ISD number, and that is easily fixed.

Which display name are you referring to?

The title at the top of the Summary tab in the black bar. Or does your workbook already accurately display the name of whatever you picked in the PS form like Oakland Schools, etc?

Okay, I understand now. Replace the userform call with this code

Public Sub CallUserForm()
Dim mpForm As ParameterSelection
Dim wsCombined As Worksheet
Dim mpTotalsRow As Long
Dim mpTotal As Double

    Set wsCombined = Worksheets("Combined")
    
    Set mpForm = New ParameterSelection
    With mpForm
    
        .Show
        If Not .Cancel Then
        
            Call FilterPivot("EnrollmentByGrade", .ISDCode, .DistrictCode, .BuildingCode)
            Call FilterPivot("TotalEnrollmentTrend", .ISDCode, .DistrictCode, .BuildingCode)
            Call FilterPivot("PivotTable1", .ISDCode, .DistrictCode, .BuildingCode) 'Race/Ethicity Makeup Trend
            Call FilterPivot("PivotTable2", .ISDCode, .DistrictCode, .BuildingCode) 'Other Demographic Enrollment Trend
        
            Worksheets("Summary").Range("B3").Value = .ISDCode
            Worksheets("Summary").Range("B4").Value = .DistrictCode
            Worksheets("Summary").Range("B5").Value = .BuildingCode
            If .ISDCode = NoSelection Or .DistrictCode = NoSelection Or .BuildingCode = NoSelection Then
            
                Worksheets("Summary").Range("B6:D8").ClearContents
            ElseIf .BuildingCode <> NoSelection Then
                
                mpTotalsRow = Worksheets("EEM").Columns(colEEM.BuildingCode).Find(.BuildingCode, After:=Worksheets("EEM").Cells(1, colEEM.BuildingCode)).Row
                Worksheets("Summary").Range("B6").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Address)
                Worksheets("Summary").Range("B7").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.City) & " " & _
                                                          Worksheets("EEM").Cells(mpTotalsRow, colEEM.State) & " " & _
                                                          Worksheets("EEM").Cells(mpTotalsRow, colEEM.Zip)
                Worksheets("Summary").Range("B8").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Phone)
            End If
            
            If .BuildingCode <> NoSelection Then
            
                mpTotalsRow = wsCombined.Columns(colCombined.BuildingCode).Find(.BuildingCode, After:=wsCombined.Cells(1, colCombined.BuildingCode)).Row
            ElseIf .DistrictCode <> NoSelection Then
            
                mpTotalsRow = wsCombined.Columns(colCombined.DistrictCode).Find(.DistrictCode, After:=wsCombined.Cells(1, colCombined.DistrictCode)).Row
            Else
            
                mpTotalsRow = wsCombined.Columns(colCombined.ISDCode).Find(.ISDCode, After:=wsCombined.Cells(1, colCombined.ISDCode)).Row
            End If
            
            With wsCombined
            
                mpTotal = .Cells(mpTotalsRow, colCombined.TotalEnrol).Value
                Worksheets("Summary").Range("H3").Value = .Cells(mpTotalsRow, colCombined.American).Value
                If mpTotal <> 0 Then
                    Worksheets("Summary").Range("I3").Value = .Cells(mpTotalsRow, colCombined.American).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I3").Value = 0
                End If
                Worksheets("Summary").Range("H4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I4").Value = 0
                End If
                Worksheets("Summary").Range("H5").Value = .Cells(mpTotalsRow, colCombined.African).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I5").Value = .Cells(mpTotalsRow, colCombined.African).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I5").Value = 0
                End If
                Worksheets("Summary").Range("H6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I6").Value = 0
                End If
                Worksheets("Summary").Range("H7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I7").Value = 0
                End If
                Worksheets("Summary").Range("H8").Value = .Cells(mpTotalsRow, colCombined.White).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I8").Value = .Cells(mpTotalsRow, colCombined.White).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I8").Value = 0
                End If
                Worksheets("Summary").Range("H9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I9").Value = 0
                End If
            End With
                
            Worksheets("Summary").Range("A1").Value = wsCombined.Cells(mpTotalsRow, colCombined.ISDName).Value & _
                                                      IIf(.DistrictCode = NoSelection, "", ", " & wsCombined.Cells(mpTotalsRow, colCombined.DistrictName).Value) & _
                                                      IIf(.BuildingCode = NoSelection, "", ", " & wsCombined.Cells(mpTotalsRow, colCombined.BuildingName).Value)
        End If
    End With
End Sub

[dovermac]

Ok, well, I still have the same error on line:
.Cells(1, 2).Value = ISDCode
So somewhere my code doesn't quite match up, perhaps on another tab? What was this code supposed to do exactly? I mean what adjustment was made?
Was this supposed to address the columns issue? If so, that would be so sweet.

[dovermac]

I spoke to my PM and he said to just do whatever you recommend. If you can tell me how to tweak these pivot tables I will. Remember, I suck at these things. :-)

[dovermac]

We need to figure out what I can do for you. I have to do something for all your help. By the way, my name is Jeff.

[Bob Phillips]

Hello Jeff, my name is Bob.

I have copied my file to DropBox, https://www.dropbox.com/s/pe70ja36dl...Dashboard.xlsm.

Download it, see if it solves your problems, and let me know what you think, whether anything is not what you need.

[dovermac]

I'm at work Bob, and that's blocked, like most things here so as soon as I get home I'll try it out. Got any hobbies Bob? I'm personally into movies, music and games.

[dovermac]

Hi Bob,
I just looked at your workbook. I'm impressed, looks great. We're just about there. Only one thing left. For the sake of this project's demo, we have to show importing 3 different files with the other user form (Data Acquisition), which is next to the PS form in the WB. That form is very basic. All you do is click Open, navigate to the file and then on top box, click the drop down arrow and pick a tab name. We are only going to be using Combined, StudentCount, and EEM. Now the source filename is something like a6578e05-0ec4-4b27-8323-c2836dacceda.csv. It then copies the contents to that tab, starting with the first blank line. My question is can you revise the existing code to somehow import these 3 files depending which one it is and convert the code columns to 5 characters? If you can get that working, that would be it! Then we need to figure out what I can do for you. :-) By the way, where are you located? I'm in Michigan as you probably guessed.

[Bob Phillips]

Jeff,

Are you saying that you just want to complete the Data Acquisition form take-on of data? open the file, clear out the old, insert the new? Or are you saying append to the data already there? Have you got a sample file that I can test with.

BTW, how are you proposing to fire these forms?

[dovermac]

The form has been coded, but isn't working exactly right. The files will always append. That part is working. The "code" columns are not converting to 5 characters. The source filenames are gibberish so that doesn't help. Here are links to 3 sample files. Download them, don't rename them and experiment. Try the Data Acquisition Form. It will append just fine, it doesn't convert the 3 code columns to 5 chars.
EEM: https://downloads.mischooldata.org/GeneratedDataFile?filename=226ef227-e6ae-468e-8351-7a416b15ac36.csv
Student Count: https://downloads.mischooldata.org/GeneratedDataFile?filename=6cc33dc0-b63f-46c3-b595-83462cb9d33d.csv
https://downloads.mischooldata.org/GeneratedDataFile?filename=44577f82-18a0-4c3f-b0f0-2a48d5de0da7.csv
This is the Combined File.

I don't understand the "how am I to fire these forms" question. can you elaborate please?

[dovermac]

Hey Bob,
Everything ok? Just curious. You need anything from me?

Jeff

[dovermac]

Hi Bob,
I'm guessing that you're no longer interested in working on this? If that's the case, just let me know. No hard feelings. I really appreciate all your help, sincerely! :-)

Jeff