Ok,
I'he highlighted some parts of the macro:
1. The green part: It's the page field name that you're trying to
synchronize. If it`s another fild type you`ll have to change PageFields to
the correct field type.
2. The yellow part: Is the value to be filtered, you can change to one
variable ou change it to a fixed value, or you can pass the value to the
function in the call
AplicaFiltros("FilterText") and make some changes to the function
declaration:
Public Sub AplicaFiltros(strFilter as string), and change the yellow part to
strFilter.
3. The Blue part: it's a error treatment, the error code 1004 is runtime
error and may have different causes, that's wy I verify the message too. If
some of the pivot tables doesn't have the green page field you'll get an
error when trying to change it. If you don't need it, just remove.
Public Sub AplicaFiltros()
Dim auxWks As Worksheet
Dim auxPivot As PivotTable
On Error GoTo AplicaFiltros_ERRO
For Each auxWks In ThisWorkbook.Worksheets
For Each auxPivot In auxWks.PivotTables
'auxPivot.RefreshTable
auxPivot.PageFields("Ano").CurrentPage = ThisWorkbook.Names
("globalAno").RefersToRange.Value
Next
Next
AplicaFiltros_EXIT:
Exit Sub
AplicaFiltros_ERRO:
If Err.number = 1004 And Err.Description = "Unable to get the
PageFields property of the PivotTable class" Then
Resume Next
End If
MsgBox Err.Description, vbCritical, "Erro ao aplicar os filtros
('" & auxWks.Name & "', '" & auxPivot.Name & "')!"
Resume AplicaFiltros_EXIT
End Sub
2009/4/22 Fabio_XL_Master <flnle...@gmail.com>
>
> Hi,
>
> It's possible using macros:
>
> Public Sub AplicaFiltros()
> Dim auxWks As Worksheet
> Dim auxPivot As PivotTable
>
> On Error GoTo AplicaFiltros_ERRO
>
> For Each auxWks In ThisWorkbook.Worksheets
> For Each auxPivot In auxWks.PivotTables
> 'auxPivot.RefreshTable
> auxPivot.PageFields("Ano").CurrentPage = ThisWorkbook.Names
> ("globalAno").RefersToRange.Value
> Next
> Next
>
> AplicaFiltros_EXIT:
>
> Exit Sub
> AplicaFiltros_ERRO:
> If Err.number = 1004 And Err.Description = "Unable to get the
> PageFields property of the PivotTable class" Then
> Resume Next
> End If
>
> MsgBox Err.Description, vbCritical, "Erro ao aplicar os filtros
> ('" & auxWks.Name & "', '" & auxPivot.Name & "')!"
> Resume AplicaFiltros_EXIT
> End Sub
>
>
> Regards!
>
> >
>
--
Fabio L Lemos
email: flnle...@gmail.com
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
