Excel VBA Aggiorna tabella pivot

Quando inseriamo una tabella pivot nel foglio, una volta che i dati cambiano i dati della tabella pivot non cambiano da soli, dobbiamo farlo manualmente ma in VBA c'è un'istruzione per aggiornare la tabella pivot che è expression.refreshtable , usando questo possiamo aggiornare il tabella pivot facendo riferimento al foglio di lavoro che la compone oppure possiamo fare riferimento alle intere tabelle pivot nei fogli di lavoro e aggiornarle tutte in una volta.

La tabella pivot è fondamentale per analizzare l'enorme quantità di dati. Aiuta direttamente dall'analisi, dal riepilogo e dall'interpretazione dei dati utili. Tuttavia, uno dei problemi con questa tabella pivot è che non verrà aggiornato automaticamente in caso di modifiche ai dati di origine, l'utente deve aggiornare la tabella pivot andando alla particolare tabella pivot ogni volta che c'è una modifica. Ma dì addio al processo manuale perché qui abbiamo il metodo per aggiornare la tabella pivot non appena apporti modifiche nella tabella pivot.

Come aggiornare automaticamente il codice VBA dei dati della tabella pivot?

L'unica volta che la tabella pivot deve essere aggiornata è ogni volta che si verifica una modifica nei dati di origine della tabella pivot a cui ci riferiamo.

Ad esempio, guarda i dati sottostanti e la tabella pivot.

Ora cambierò i numeri nei dati di origine, cioè da A1 a B17.

Nella cella B9 devo cambiare il valore da 499 a 1499 cioè 1000 aumento dei dati ma se guardi il pivot mostra ancora il risultato come 4295 invece di 5295. Devo aggiornare manualmente la mia tabella pivot per aggiornare la tabella pivot.

Per superare questo problema, è necessario scrivere un semplice codice macro Excel per aggiornare la tabella pivot ogni volta che viene apportata una modifica ai dati di origine.

Puoi scaricare questo modello Excel VBA Aggiorna tabella pivot qui - VBA Aggiorna modello Excel tabella pivot

# 1 - Macro semplice per aggiornare tutta la tabella

Passaggio 1: modifica dell'evento del foglio dati

Dobbiamo attivare l'evento di modifica del foglio dati. Nell'editor di visual basic fare doppio clic sul foglio dati.

Dopo aver fatto doppio clic sul foglio selezionare "Foglio di lavoro" e selezionare l'evento come "Modifica".

Vedrai una procedura secondaria automatica aperta come Worksheet_Change (ByVal Target As Range)

Passaggio 2: utilizzare l'oggetto foglio di lavoro

Fare riferimento al foglio dati utilizzando l'oggetto Fogli di lavoro.

Passaggio 3: fare riferimento alla tabella pivot per nome

Fare riferimento al nome della tabella pivot con il nome della tabella pivot.

Passaggio 4: utilizzare il metodo Aggiorna tabella

Seleziona il metodo come "Aggiorna tabella".

Ora, questo codice aggiornerà la tabella pivot "Tabella pivot1" ogni volta che viene apportata una modifica nel foglio dati di origine. Puoi usare il codice sottostante, devi solo cambiare il nome della tabella pivot.

Codice:

 Foglio di lavoro secondario privato_Cambia (per valore target come intervallo) Foglio di lavoro ("Foglio dati"). Tabelle pivot ("Tabella pivot1"). Aggiorna tabella End Sub

# 2 - Aggiorna tutte le tabelle pivot dello stesso foglio di lavoro

Se hai molte tabelle pivot nello stesso foglio di lavoro, puoi aggiornare tutte le tabelle pivot con un solo clic stesso. Usa il codice seguente per aggiornare tutte le tabelle pivot nel foglio.

Codice:

 Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Selezionare With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub

È necessario modificare il nome del foglio di lavoro e i nomi delle tabelle pivot secondo i dettagli del foglio di lavoro.

# 3 - Aggiorna tutte le tabelle nella cartella di lavoro

È altamente improbabile che tutte le tabelle pivot siano presenti sullo stesso foglio di lavoro. Di solito, per ogni rapporto, proviamo ad aggiungere tabelle pivot separate in fogli separati. In questi casi, non possiamo continuare a scrivere il codice per ogni tabella pivot da aggiornare.

Quindi, quello che possiamo fare è con un singolo codice usando i loop, possiamo scorrere tutte le tabelle pivot sulla cartella di lavoro e aggiornarle con un solo clic del pulsante.

Il codice seguente scorrerà attraverso ciascuna tabella pivot e le aggiornerà.

Codice 1:

 Sub Refresh_Pivot_Tables_Example2 () Dim PT come tabella pivot per ogni PT in ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub

Codice 2:

 Sub Refresh_Pivot_Tables_Example3 () Dim PC come PivotCache per ogni PC in ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub

Entrambi i codici eseguiranno l'aggiornamento delle tabelle pivot.

Se si desidera che la tabella pivot venga aggiornata non appena ci sono modifiche nel foglio dati del foglio pivot, è necessario copiare e incollare i codici precedenti nell'evento Modifica foglio di lavoro in quella cartella di lavoro.

# 4 - Evita il tempo di caricamento utilizzando Evento Disattiva foglio di lavoro

Quando utilizziamo l'evento "Modifica foglio di lavoro", continua ad aggiornarsi anche quando non ci sono modifiche nell'origine dati, ma se si verificano modifiche nel foglio di lavoro.

Anche se inserisci un singolo punto nel foglio di lavoro, prova ad aggiornare la tabella pivot. Quindi, per evitare ciò, possiamo utilizzare il metodo "Disattivazione foglio di lavoro" invece del metodo "Modifica foglio di lavoro".