› Sviluppare funzionalita su Microsoft Office con VBA › Filtrare 2 Tabelle contemporaneamente con AutoFilter
-
AutoreArticoli
-
Buonasera a tutti,
sono un novizio nella programmazione di VBA e sto cercando di eseguire un'operazione molto semplice:
avendo 2 tabelle (dette Tabella1 e Tabella2) , se la colonna x della Tabella1 viene filtrata allora la colonna y della Tabella2 viene filtrata con lo stesso criterio della prima tabella.
Voglio triggerare questa procedura ogni volta che un subtotale viene ricalcolato, in particolare il subtotale associato al numero di righe di Tabella1 ( in modo che ogni volta che filtro Tabella1 automaticamente filtro Tabella2).
Come evento uso " Private Sub Workbook_SheetCalculate " poichè avrò molti Fogli a cui applicherò
la procedura e questa procedura si attiva ogni volta che il subtotale viene ricalcolato.
Ho pensato a questa soluzione per collegare 2 tabelle in modo da "associare" i filtri, ma purtroppo il codice non funziona: infatti quando applico AUTOFILTER alla Tabella2 per Field = 1 viene applicato AUTOFILTER al Field = 1 della Tabella1 , Tabella2 non viene filtrata affatto e Tabella1 collassa alla riga delle intestazioni.Ho semplificato quindi il problema creando 2 tabelle semplici e provato solo a filtrare Tabella2 con il criterio A1, ma filtra Tabella1
Sapreste spiegarmi il problema?
Grazie
IvanPrivate Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Dim ActiveSh As Worksheet Set ActiveSh = Sh Dim Tab1 As String Dim Tab2 As String With ActiveSh Tab1 = .ListObjects(1).Name Tab2 = .ListObjects(2).Name .ListObjects(Tab2).Range.AutoFilter Field:=1, Criteria1:="A1" End With Application.EnableEvents = True End Sub
Allegati:
You must be logged in to view attached files.Non ho ancora esaminato i file per rendermi conto dello scenario, ma penso che ci sia dovuto al fatto che può esserci solo una tabella autofiltrata (una sola alla volta, non due indipendenti) sul foglio. Però vedo che utilizzi ListObjects quindi stai parlando di tabelle e non di intervalli... quindi meglio che mi guardo il file 😀
ciao a tutti,
ciao Vecchio Frac,
lavorando non ho ancora avuto tempo per risolverlo, ma il problema secondo me è sull'evento del foglio.
ma penso che ci sia dovuto al fatto che può esserci solo una tabella autofiltrata (una sola alla volta, non due indipendenti) sul foglio.
due tabelle sullo stesso foglio, che ovviamente non condividano le stesse righe, in un modulo standard e con il codice adeguato,
(quello di Ivan funziona) vengono filtrate contemporaneamente.
Ciao
Frank
due tabelle sullo stesso foglio, ..., vengono filtrate contemporaneamente.
Sono tabelle nel senso stretto e quindi funzionano in modo diverso dal semplice intervallo... io non le uso praticamente mai 🙂
Volevo scrivere anch'io qualche perplessità sull'evento indicato, ma ho pensato magari che non si può intercettare il cambiamento della cella subtotale perchè può darsi che sia destinata a mutare col tempo.
Ciao Vecchio Frac,
così funziona:
`Private Sub Worksheet_Change(ByVal Target As Range) Dim ActiveSh As Worksheet Set ActiveSh = ActiveSheet Dim Tab1 As String Dim Tab2 As String With ActiveSh Tab1 = .ListObjects(1).Name Tab2 = .ListObjects(2).Name .ListObjects(Tab1).Range.AutoFilter Field:=1, Criteria1:="Ave" .ListObjects(Tab2).Range.AutoFilter Field:=1, Criteria1:="A1" End With End Sub `
allego file
Sono tabelle nel senso stretto e quindi funzionano in modo diverso dal semplice intervallo... io non le uso praticamente mai
stessa cosa per me, mai usate tabelle.
Ciao
Frank
Allegati:
You must be logged in to view attached files.Private Sub Worksheet_Change(ByVal Target As Range)
Ecco bravo, volevo proporlo io. L'unico inconveniente di questa routine è che si scatena ogni volta che si modifica una cella del foglio. Per questo immaginavo di intercettare solo il cambiamento della cella subtotale (come descritto dall'OP), e mi chiedevo se esisteva un metodo che permette di individuarla dinamicamente (perchè tale cella subtotale può variare al variare della tabella stessa).
Per questo immaginavo di intercettare solo il cambiamento della cella subtotale (come descritto dall'OP), e mi chiedevo se esisteva un metodo che permette di individuarla dinamicamente (perchè tale cella subtotale può variare al variare della tabella stessa).
ciao Vecchio Frac,
purtroppo non ho tempo per testarla e l'ho solo pensata:
forse si potrebbero creare una variabile publica con la quale memorizzare il valore del sub totale all'apertura del file,
e fare scattare il filtro delle tabelle solo se sull'evento change del foglio interessato, la variabile è cambiata.
Quindi solo se il change del foglio cambia la cella sub totale, dovrebbe bastare un IF.... Then.
Ciao
Frank
Se devo lavorare con una tabella come potrei fare? Farla diventare Range, o comunque togliere il formato tabella, Poi filtrarla e Poi rifarla diventare Tabella? La tabella1 che andrei usare nella realtà ha oltre 400 righe e gestisce i lavoratori di un'azienda , quindi ho pensato di metterla come Tabella per poterla filtrare più facilmente in base al turno, al reparto e altre cose.
stessa cosa per me, mai usate tabelle.
Ciao a tutti,
ciao Ivan,
potrei avere il tuo parere sul file che ho allegato alla risposta 21137?
Si puo' migliorare, ma mi sembra faccia quello che hai chiesto.
ciao
Frank
IvanBolzoni ha scritto:
Se devo lavorare con una tabella come potrei fare? Farla diventare Range, o comunque togliere il formato tabella, Poi filtrarla e Poi rifarla diventare Tabella? La tabella1 che andrei usare nella realtà ha oltre 400 righe e gestisce i lavoratori di un'azienda , quindi ho pensato di metterla come Tabella per poterla filtrare più facilmente in base al turno, al reparto e altre cose.
stessa cosa per me, mai usate tabelle.
edit by VF: volevo editare il messaggio di Ivan per formattarlo meglio ma il parser ha combinato un pasticcio, così adesso il messaggio originale risulta scritto da me
Comunque, non conviene trasformare tabelle in range e viceversa di continuo, basterebbe studiarsi un attimo il meccanismo della tabella e capire quali sono i metodi migliori per gestirla. Aspettiamo il parere di Ivan sul codice di tanimon 🙂
Grazie a tutti per l'interessamento, Tanimon, domani ti faccio sapere 😉
Buonasera,
ho appena testato il codice di Tanimon e non filtra nessuna tabella quando filtro Tabella1.
L'evento Change credo si avvii quando una cella viene riscritta o cambiata, ma non funziona quando la cella viene cambiata mediante formula. Quindi questa procedura non viene lanciata né quando applico il filtro e né quando il subtotale nella cella A1 viene ricalcolato, che in teoria è quello che mi serve. Perciò le domande sono 2:
1) Come mai Autofilter non funziona con 2 tabelle diverse chiamandolo su una procedura Calculate?
Ho letto che Autofilter è Volatile e che lancia automaticamente l'evento Calculate, perciò ho disabilitato tutti gli eventi. Tuttavia questo accorgimento non funziona: potrebbe essere che Autofilter venga associato una volta per tutte a Tabella1... posso provare a inserire una sotto procedura che applichi Autofilter a Tabella2
2) Riuscite a trovare magari un altro metodo che colleghi le Tabelle senza dover cambiare alcuna cella?
io ho pensato al ricalcolo di un subtotale che poi andrò a nascondere perché poi l'utente che userà questa procedura non deve modificare una cella ogni volta, deve solo modificare il filtro associato a una determinata colonna di Tabella 1.
Cogitate…
Voglio triggerare questa procedura ogni volta che un subtotale viene ricalcolato, in particolare il subtotale associato al numero di righe di Tabella1 ( in modo che ogni volta che filtro Tabella1 automaticamente filtro Tabella2).
ciao,
prova il file allegato e vediamo a che punto siamo.
ciao
Frank
Allegati:
You must be logged in to view attached files.ciao Frank,
grazie per i tuoi continui sforzi ma il tuo file mi da un errore che mi si era già presentato:
Errore di run time '-2147417848 (80010108)
Metodo "Autofilter" dell'oggetto "Range" non riuscito
Il problema è fastidioso perchè in pratica si verifica una ricorsione infinita.
Devi disabilitare gli eventi con Application.EnableEvents = False all'inizio della sub e ripristinare gli eventi prima di uscire con Application.EnableEvents = True.
In effetti è vero, e me n'ero dimenticato, e chiedo scusa, che il change di una cella dovuto a formula non scatena l'evento Change.
L'ho fatto con il codice che ho postato all'inizio:infatti non mi dà più quell' errore ma mi continua a filtrare la Tabella1 e non Tabella2
ciao a tutti
ciao Ivan ed un saluto a VecchioFrac
in allegato il file e di seguito il codice che sono riuscito a partorire, ma per le tabelle su 2 fogli diversi.
In rete sono incappato in un post di un Forum , nel quale Mauro Gamberini sconsiglia SEMPRE l'utilizzo di due tabelle
sullo stesso foglio.
Dopo questo mi arrendo
Ciao
Frank
Private Sub Worksheet_Calculate() Dim ur_count As Long, ur1 As Long, ur_vis As Long Application.ScreenUpdating = False ur_count = ActiveSheet.ListObjects(1).Range.Rows.Count ur1 = ActiveSheet.Range("a" & ur_count).End(xlDown).Row ur_vis = Range("a" & ur1).Cells.SpecialCells(xlCellTypeVisible).Rows.Count If ur_vis <> ur_count Then Sheets("Foglio2").Activate Sheets("Foglio2").ListObjects(1).Range.AutoFilter Field:=1, Criteria1:="A1" End If Application.ScreenUpdating = True End Sub
Allegati:
You must be logged in to view attached files.ciao,
prova e poi mi arrendo.
in rete Muaro Gamberini sconsiglia sempre due tabelle sullo stesso foglio.
Il file allegato ne ha due MA su 2 fogli diversi.
ciao
Frank
`rivate Sub Worksheet_Calculate() Dim ur_count As Long, ur1 As Long, ur_vis As Long Application.ScreenUpdating = False ur_count = ActiveSheet.ListObjects(1).Range.Rows.Count ur1 = ActiveSheet.Range("a" & ur_count).End(xlDown).Row ur_vis = Range("a" & ur1).Cells.SpecialCells(xlCellTypeVisible).Rows.Count If ur_vis <> ur_count Then Sheets("Foglio2").Activate Sheets("Foglio2").ListObjects(1).Range.AutoFilter Field:=1, Criteria1:="A1" End If Application.ScreenUpdating = True End Sub `
Allegati:
You must be logged in to view attached files.Buongiorno,
Voglio ringraziare tutti per l'attenzione, soprattutto Frank per i continui sforzi e il tempo dedicatomi.
La soluzione che hai proposto non mi servirebbe perchè il problema reale è questo: aggiungere la Tabella2 sotto la Tabella1 in modo che quando si stampa il foglio esse escano filtrate una sopra l'altra.
Il numero di fogli che avrò sarà pari al numero di giorni del mese quindi mi ritroverei con almeno 60 fogli! Usando l'ultima soluzione
Detto questo ripartiamo da capo. Potrei aver impostato male i presupposti del problema e rivalutiamo tutto seguendo l'hint finale di Frank
Il file originale contiene solo Tabella1 e Tabella2 l'ho creata io, potrei anche non crearla come Tabella2 e tenere solo un range che sembra una tabella.
Quindi senza dichiarare Tabella2 posso filtrare questo nuovo "Range"?
Se si Come?
Grazie ancora
ciao Ivan,
per quello che ho capito, le tabelle non sono necessarie e probabilmente neanche i filtri.
Ripartendo:
allega un file con 3 range compilati a mano di cui l'ultimo è il risultato che vuoi ottenere,
per il quale non ti dimenticare i criteri secondo i quali alcuni dati dei 2 range di partenza devono andare nel terzo,
ed altri non ci devono andare.
Ciao
Frank
sicuramente Tabella1 è una tabella, quella non posso cambiarla perché nel foglio su cui lavorerò è una tabella con molte colonne filtrabili
Poi con una macro creo un range che raccoglie le frequenze di ogni reparto per ogni turno : ad esempio se in quel giorno nel turno A1 10 operai lavorano alle 14, 8 alle 20 e 2 sono in ferie esce
Reparto 14 20 Ferie Malattia A1 10 8 2 0
Operazione che faccio per tutti i reparti
Infine trasformo il range in tabella (cosa che mi hai sconsigliato di fare).
A questo punto ho Tabella1 e un range con le frequenze che voglio filtrare in base al filtro reparto applicato alla Tabella1. Questa è la situazione
ciao,
procedimento:
1 applica il filtro a Tabella1 per una singola voce
2 converti la Tabella2 in range classico di cui la prima riga deve essere la 18
3 lancia la macro che ti riporto di seguito inserita in un modulo standard
prova e fai sapere
ciao
Frank
Sub a() Dim ur As Long, ur1 As Long, ur_vis As Long Dim criterio As String ur1 = ActiveSheet.Range("a6").End(xlDown).Row ur_vis = ActiveSheet.Range("a6:c" & ur1).Cells.SpecialCells(xlCellTypeVisible).Row criterio = Cells(ur_vis, 2).Value ur = Range("a" & Rows.Count).End(xlUp).Row For r = 18 To ur If Cells(r, 1).Value = criterio Then Range("a" & r & ":" & "e" & r).Copy Cells(ur1 + 2, 1) End If Next r End Sub
aggiunge delle righe che non si cancellano alla riga 9 e 10, a me servirebbe che comparissero solo quando il filtro è applicato e che appaiano al posto del range, non sotto Tabella1
Ora penso a qualcosa e ti faccio sapere 😉
-
AutoreArticoli