Attribute VB_Name = "FiltradoTablasDinamicas"Option Explicit

Public Sub CopiarFiltroPaginaEnRestoTablasDinamicas() Const TITULO = "CopiarFiltroPaginaEnRestoTablasDinamicas"

Dim hoja As Worksheet Dim tablaOrigen As PivotTable, tablaDestino As PivotTable Dim paginaOrigen As PivotField Dim campoOrigen As PivotField, campoDestino As PivotField Dim itemOrigen As PivotItem, itemDestino As PivotItem Dim valoresOrigen As Variant 'Array Application.ScreenUpdating = False On Error GoTo FaltaTablaDinamica Set tablaOrigen = ActiveSheet.PivotTables(1) On Error GoTo 0 For Each paginaOrigen In tablaOrigen.PageFields Set campoOrigen = tablaOrigen.PivotFields(paginaOrigen.Name) '''campoOrigen.AutoSort xlManual, campoOrigen.SourceName If campoOrigen.AllItemsVisible Then 'TODO For Each hoja In ActiveWorkbook.Worksheets For Each tablaDestino In hoja.PivotTables If tablaDestino <> tablaOrigen Then If ExisteFiltroPagina(tablaDestino, paginaOrigen.Name) Then Set campoDestino = tablaDestino.PivotFields(paginaOrigen.Name) '''campoDestino.AutoSort xlManual, campoDestino.SourceName campoDestino.ClearAllFilters 'SIN FILTRAR End If End If Next Next ElseIf campoOrigen.EnableMultiplePageItems Then 'MULTIPLE SELECCI�N valoresOrigen = ObtenerArrayValoresDelCampoDinamico(campoOrigen) For Each hoja In ActiveWorkbook.Worksheets For Each tablaDestino In hoja.PivotTables If tablaDestino <> tablaOrigen Then If ExisteFiltroPagina(tablaDestino, paginaOrigen.Name) Then Set campoDestino = tablaDestino.PivotFields(paginaOrigen.Name)

'''campoDestino.AutoSort xlManual, campoDestino.SourceName campoDestino.ClearAllFilters campoDestino.CurrentPage = "(All)" For Each itemDestino In campoDestino.PivotItems If Not EstaEnArrayOrdenado(itemDestino.Name, valoresOrigen) Then On Error Resume Next '??? itemDestino.Visible = False End If Next End If End If Next Next Else 'UNA SELECCI�N For Each hoja In ActiveWorkbook.Worksheets For Each tablaDestino In hoja.PivotTables If tablaDestino <> tablaOrigen Then If ExisteFiltroPagina(tablaDestino, paginaOrigen.Name) Then Set campoDestino = tablaDestino.PivotFields(paginaOrigen.Name) '''campoDestino.AutoSort xlManual, campoDestino.SourceName campoDestino.ClearAllFilters On Error Resume Next 'Por si no existiese campoDestino.CurrentPage = CStr(campoOrigen.CurrentPage) End If End If Next Next End If NextSalida: Application.ScreenUpdating = True Exit SubFaltaTablaDinamica: MsgBox "No hay ninguna tabla din�mica en la hoja actual", vbExclamation, TITULO Resume SalidaEnd Sub Private Function ObtenerArrayValoresDelCampoDinamico(campo As PivotField) As Variant Const DELIMITADOR = ";"

Dim values As String Dim item As PivotItem For Each item In campo.PivotItems If item.Name = "(blank)" Then ElseIf item.Visible Then If values = "" Then values = item.Name Else values = values & DELIMITADOR & item.Name End If End If Next ObtenerArrayValoresDelCampoDinamico = Split(values, DELIMITADOR)End Function

Function EstaEnArrayOrdenado(buscado As String, arr As Variant) As Boolean Dim Value As Variant For Each Value In arr If buscado = CStr(Value) Then EstaEnArrayOrdenado = True Exit Function ElseIf buscado < CStr(Value) Then Exit For End If Next EstaEnArrayOrdenado = FalseEnd Function

Private Function ExisteFiltroPagina(tabladin As PivotTable, nombreCampo As String) As Boolean Dim campodin As PivotField For Each campodin In tabladin.PageFields If campodin.Name = nombreCampo Then ExisteFiltroPagina = True Exit Function End If Next ExisteFiltroPagina = FalseEnd Function

'===============================================

'Ventana inmediato'? ActiveWorkbook.Worksheets("tdReal").PivotTables(1).PivotFields("Canal").CurrentPage'? ActiveWorkbook.Worksheets("tdReal").PivotTables(1).PivotFields("Canal").AllItemsVisible