• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Controlling Excel Pivot Table Values Using one cell

L

Lazar1us

New Member
Hi All,

Very new to the forums here and would like to say hello with a somewhat complicated question.

The previous guy at my job has created a very large spreadsheet containing data in multiple pivot tables (about 45-50 tables in total). Users of the spreadsheet needs to go through all these tables when they only need to change one thing (i.e. Company Name) for the data on the dashboard to show properly.

I tried using the code below, thinking that I just need to add pivot tables one by one on the Set part, but got a runtime error instead.

Any help would be greatly appreciated! Thank you!


Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("O6:O7")) Is Nothing Then Exit Sub


Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


Set pt = Worksheets("Sheet4").PivotTables("PivotTable8")
Set Field = pt.PivotFields("Company Code")
NewCat = Worksheets("Sheet1").Range("O6").Value


With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub
 
Hi ,

If you want a VBA error resolved , please do the following :

1. Upload a workbook which has data and the code in it

2. If the above is not possible , highlight the line of code which generates the error.

Narayan
 
Hi ,

Probably the sheet does not exist , or the PivotTable8 does not exist on the tab named Sheet4.

If you upload your workbook , you will get a solution faster.

Narayan
 
Here's the sample file that I was talking about. I had to edit some info out due to work policy etc.

Also, the file is too big to be uploaded so I've had to paste a link to dropbox:
https://www.dropbox.com/s/327nx2i4555sdfj/AM Review Sample.xlsx?dl=0

Same thing - I want the table on Sheet1 to update when I enter the company code value on Sheet1 - Cell F5

The pivot tables to update are all on Sheet7 - Pivot Transaction

Finally here's the macro script:


Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Intersect(Target, Range("F5:F6")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


Set pt = Worksheets("Sheet7").PivotTables("PivotTable50")
Set Field = pt.PivotFields("Company Code")
NewCat = Worksheets("Sheet1").Range("F5").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub
 
Hi ,

Minor mistakes here and there :

Private Sub Worksheet_Change(ByVal Target As Range)
Code: 
            If Intersect(Target, Range("F5:F6")) Is Nothing Then Exit Sub

            Dim pt As PivotTable
            Dim Field As PivotField
            Dim NewCat As String
Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")
Set Field = pt.PivotFields("Company Name")
NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value
Code: 
'           This updates and refreshes the PIVOT table
            With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 pt.RefreshTable
            End With
End Sub
Narayan
The syntax for worksheet addressing is :

1. Worksheets( TABNAME )

2. SheetName

Thus , you need to use either of the following :

Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")

Set pt = Sheet7.PivotTables("PivotTable50")

Narayan
 
Hi Narayan,

Apologies for the lateness of my reply. I've just come back from overseas and back to the swing of things.

The code doesn't appear to work - I've tried running it and PivotTable50 does not update to the wanted Company Name. Perhaps I've done something wrong, but will have a look at it again when I come back from work and update my progress.

Thank you in advance!


Regards,
Adrian
 
Hi Adrian ,

I assume there is some confusion because of the way I posted the code ; I am reposting the code which needs to be used. In my earlier post , I separated the code and highlighted the portions which needed to be changed.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
            If Intersect(Target, Range("F5:F6")) Is Nothing Then Exit Sub

            Dim pt As PivotTable
            Dim Field As PivotField
            Dim NewCat As String

            Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")
            Set Field = pt.PivotFields("Company Name")
            NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value

'           This updates and refreshes the PIVOT table
             With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 .RefreshTable
            End With
End Sub
Narayan
 
Hi Narayan,

Thank you for this - the code worked perfectly!

If I am to update two pivot tables now, do I use the following:

Code: 
PrivateSub Worksheet_Change(ByVal Target As Range)
           If Intersect(Target, Range("F5:F6")) IsNothingThenExitSub

           Dim pt As PivotTable
           Dim Field As PivotField
           Dim NewCat AsString

           Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")
           Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable48")
           Set Field = pt.PivotFields("Company Name")
            NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value

'           This updates and refreshes the PIVOT table           
            With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 .RefreshTable
           EndWith
EndSub


or would it be better for me to repaste the whole thing like so:

Code: 
PrivateSub Worksheet_Change(ByVal Target As Range)
           If Intersect(Target, Range("F5:F6")) IsNothingThenExitSub

           Dim pt As PivotTable
           Dim Field As PivotField
           Dim NewCat AsString

           Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")
           Set Field = pt.PivotFields("Company Name")
            NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value

           Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable48")
           Set Field = pt.PivotFields("Company Name")
            NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value


'           This updates and refreshes the PIVOT table
           With pt
                 Field.ClearAllFilters
                 Field.CurrentPage = NewCat
                 .RefreshTable
           EndWith
EndSub

Thank you in advance!

Regards,
Adrian
 
Hi Adrian ,

I think it would be something like this , assuming that both pivot tables have the same source data , and the Company Name field.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
            If Intersect(Target, Range("F5:F6")) Is Nothing Then Exit Sub

            Dim pt As PivotTable
            Dim Field As PivotField
            Dim NewCat As String

            NewCat = Worksheets("Trending&Benchmarking").Range("F5").Value

'          --------------------------------------------------------------------
'          The next section updates PivotTable50
'          --------------------------------------------------------------------
            Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable50")
            Set Field = pt.PivotFields("Company Name")

'          This updates and refreshes the PIVOT table
            With pt
                Field.ClearAllFilters
                Field.CurrentPage = NewCat
                .RefreshTable
            End With

'          --------------------------------------------------------------------
'          The next section updates PivotTable48
'          --------------------------------------------------------------------
            Set pt = Worksheets("Pivot Transaction").PivotTables("PivotTable48")
            Set Field = pt.PivotFields("Company Name")

'          This updates and refreshes the PIVOT table
            With pt
                Field.ClearAllFilters
                Field.CurrentPage = NewCat
                .RefreshTable
            End With
End Sub
Narayan
 
Hi Narayan,

This is fantastic! Everything has worked perfectly. Thank you for this!

One last question that I have is would it be possible to enter more than one company code on the target cell (F5:F6) so that in turn the Macro populates both of those into the Pivot Tables?


Regards,
Adrian
 
Dear Narayan,

I have followed your instructions as above but I still get an yellow line with error message.

Could you help?

The different points are below.

1. Sheet "ProdC" has "PivotTable3"
2. Sheet "OneView" has a variant.
3. The variant are normally six digit numbers e.g. 111111 rarely 4 or 5 digits / 1111 / 11111

Below is what i have designed.

--------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As Long


Set pt = Worksheets("ProdC").PivotTables("PivotTable3")
Set Field = pt.PivotFields("Product")
NewCat = Worksheets("OneView").Range("A1").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
.RefreshTable
End With


End Sub

-----------------------------------------------------------------------------

And I get error message for the line (Field.CurrentPage = NewCat)

Could you help??
 
Hi ,

I am not sure where the problem can be ; it may be that the type of the category in the pivot table may be String , but this is just a guess.

Can you upload your workbook with the source data and the pivot table ?

Narayan
 
Hello Narayank991.
OK. As the original informatio is sensitive, I have modified and updated it as attached. You can either choose below 2 poducts and input them in A1 cell in sheet " Oneview" to change the pivottable3 in ProdC sheet.

304114
304329

Please look it up and help. Many thanks!
 

Attachments

  • FCST MT Preparation_1507_00.xlsm
    255 KB · Views: 3
Hi Narayank991
Thank you very much!
I copied ad pastd the codes you made into the orinal file.
It works... but isues is that it takes around 4.5 minutes to run the VBA taing CPU up to 85%.. maybe it's because the file size is 24MB.. but i'm not sure why it takes more time to run the VBA than I do it manually.

I will try to minimize the file size first. and run it agan. Anyhow, I appreciate your help!
 
Late to the party But I found this code to work for me and it's quite fast

Code: 
With pt
Field.ClearAllFilters
'Makes sure that if the cell value is deleted it doesn't error
If Range("A1").Value = vbNullString Then
Exit Sub
Else
Field.CurrentPage = NewCat
End If
pt.RefreshTable
End With
 
You must log in or register to reply here.
Back
Top