Come Auto Refresh tabelle pivot che utilizza VBA di Excel
Come tutti sappiamo, ogni volta che apportiamo modifiche ai dati di origine di una tabella pivot, non si riflettono immediatamente nella tabella pivot. Dobbiamo aggiornare le tabelle pivot per vedere le modifiche. E se invii un file aggiornato senza aggiornare le tabelle pivot, potresti provare imbarazzo. Quindi, in questo articolo, impareremo come aggiornare automaticamente una tabella pivot utilizzando VBA. In questo modo è più facile di quanto immaginassi.
Questa è la semplice sintassi per aggiornare automaticamente le tabelle pivot nella cartella di lavoro.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
Cosa sono le cache pivot? Ogni tabella pivot memorizza i dati nella cache pivot. Questo è il motivo per cui pivot è in grado di mostrare i dati precedenti. Quando aggiorniamo le tabelle pivot, aggiorna la cache con i nuovi dati di origine per riflettere le modifiche sulla tabella pivot.
Quindi abbiamo solo bisogno di una macro per aggiornare la cache delle tabelle pivot. Lo faremo usando un link: / events-in-vba-the-worksheet-events-in-excel-vba [evento foglio di lavoro] in modo da non dover eseguire la macro manualmente. === Dove codificare per aggiornare automaticamente le tabelle pivot? Se i dati di origine e le tabelle pivot si trovano in fogli diversi, il codice VBA dovrebbe andare nel foglio dati di origine.
Qui useremo Worksheet_SelectionChange Event. In questo modo il codice verrà eseguito ogni volta che si passa dal foglio dati sorgente a un altro foglio.
Spiegherò più tardi perché ho utilizzato questo evento.
Qui, ho i dati di origine in sheet2 e tabelle pivot in sheet1.
Apri VBE usando CTRL + F11. In Esplora progetti, puoi vedere tre oggetti, Foglio1, Foglio2 e la cartella di lavoro.
Poiché Sheet2 contiene i dati di origine, fare doppio clic sull’oggetto Sheet2.
Ora puoi vedere due menu a discesa nella parte superiore dell’area del codice. Dal primo menu a discesa, seleziona il foglio di lavoro. E dal secondo menu a discesa seleziona Disattiva. Questo inserirà un sotto nome vuoto Worksheet_Deactivate. Il nostro codice sarà scritto in questo sottotitolo. Qualsiasi riga scritta in questo sottotitolo, viene eseguita non appena l’utente passa da questo foglio a qualsiasi altro foglio.
Su sheet1 ho due tabelle pivot. Voglio aggiornare solo una tabella pivot. Per questo, ho bisogno di conoscere il nome della tabella pivot. Per conoscere il nome di qualsiasi tabella pivot, seleziona qualsiasi cella in quella tabella pivot vai alla scheda di analisi della tabella pivot. Sul lato sinistro, vedrai il nome della tabella pivot. Puoi anche modificare il nome della tabella pivot qui.
Ora che conosciamo il nome della tabella pivot, possiamo scrivere una semplice riga per aggiornare la tabella pivot.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Ed è fatto.
Ora ogni volta che passerai dai dati di origine, questo codice vba verrà eseguito per aggiornare la tabella pivot1. Come puoi vedere nella gif qui sotto.
Come aggiornare tutte le tabelle pivot nella cartella di lavoro? Nell’esempio precedente, volevamo aggiornare solo una tabella pivot specifica. Ma se vuoi aggiornare tutte le tabelle pivot in una cartella di lavoro, devi solo apportare lievi modifiche al tuo codice.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
In questo codice stiamo usando un link: / vba-for-loops-with-7-examples [For loop] per scorrere ogni cache pivot nella cartella di lavoro. L’oggetto ThisWorkbook contiene tutte le cache pivot. Per accedervi utilizziamo ThisWorkbook.PivotCaches.
Perché utilizzare Worksheet_Deactivate Event?
Se desideri aggiornare la tabella pivot non appena vengono apportate modifiche ai dati di origine, dovresti utilizzare l’evento Worksheet_Change. Ma io non lo consiglio. Farà in modo che la tua cartella di lavoro esegua il codice ogni volta che apporti modifiche al foglio. Potresti dover fare centinaia di modifiche prima di voler vedere il risultato. Ma Excel aggiornerà la tabella pivot ad ogni modifica. Ciò comporterà uno spreco di tempo e risorse per l’elaborazione. Quindi, se hai tabelle pivot e dati in fogli diversi, è meglio usare Evento Disattivazione foglio di lavoro. Ti permette di finire il tuo lavoro. Una volta che si passa ai fogli della tabella pivot per vedere le modifiche, le modifiche vengono modificate.
Se hai tabelle pivot e dati di origine sullo stesso foglio e desideri che le tabelle pivot si aggiornino automaticamente, puoi utilizzare `link: / tips-using-worksheet-change-event-to-run-macro-when- any-change-is-made [Worksheet_Change Event] `.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Come aggiornare tutto nelle cartelle di lavoro quando viene apportata una modifica ai dati di origine? Se desideri aggiornare tutto su una cartella di lavoro (grafici, tabelle pivot, formule, ecc.) Puoi utilizzare il comando ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Tieni presente che questo codice non modifica l’origine dati. Quindi, se aggiungi dati sotto i dati di origine, questo codice non includerà quei dati automaticamente. Puoi usare link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] per memorizzare i dati di origine. Se non vuoi usare le tabelle, possiamo usare VBA anche per includere nuovi dati. Lo impareremo nel prossimo tutorial.
Quindi sì amico, è così che puoi aggiornare automaticamente le tabelle pivot in Excel. Spero di essere stato abbastanza esplicativo e questo articolo ti è stato utile. Se hai domande su questo argomento, puoi chiedermelo nella sezione commenti qui sotto.
Articoli correlati:
Come aggiornare dinamicamente l’intervallo di origine dati della tabella pivot in Excel: per modificare dinamicamente l’intervallo di dati di origine delle tabelle pivot, utilizziamo le cache pivot. Queste poche righe possono aggiornare dinamicamente qualsiasi tabella pivot modificando l’intervallo di dati di origine. In VBA usa gli oggetti delle tabelle pivot come mostrato di seguito …
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Run Macro If Any Change Made on Sheet in Specified Range]: * Nelle tue pratiche VBA, lo faresti ottenere la necessità di eseguire macro quando un determinato intervallo o cella cambia. In tal caso, per eseguire le macro quando viene apportata una modifica a un intervallo di destinazione, utilizziamo l’evento di modifica.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Run Macro When any Change is Made On Sheet] | Quindi, per eseguire la macro ogni volta che il foglio viene aggiornato, utilizziamo gli eventi del foglio di lavoro di VBA.
link: / events-in-vba-semplice-vba-code-to-highlight-current-row-and-column-using [Codice VBA più semplice per evidenziare riga e colonna correnti utilizzando] | Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.
link: / events-in-vba-the-worksheet-events-in-excel-vba [Gli eventi del foglio di lavoro in Excel VBA] | Gli eventi del foglio di lavoro sono davvero utili quando si desidera che le macro vengano eseguite quando si verifica un evento specificato sul foglio.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività] | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel] | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli. link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Contare i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare un valore specifico.
La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.