Come aggiornare dinamicamente lorigine dati di tutte le tabelle pivot in Excel

In un articolo precedente abbiamo appreso come modificare e aggiornare dinamicamente singole tabelle pivot con origini dati che si restringono o si espandono.

In questo articolo vedremo scopri come fare in modo che tutte le tabelle pivot in una cartella di lavoro cambino automaticamente lorigine dati. In altre parole, invece di modificare una tabella pivot alla volta, proveremo a modificare lorigine dati di tutte le tabelle pivot nella cartella di lavoro per includere dinamicamente nuove righe e colonne aggiunte alle tabelle di origine e riflettere immediatamente la modifica nelle tabelle pivot.

Scrivi codice nel foglio dati sorgente

Dato che vogliamo che questo sia completamente automatico, useremo moduli foglio per scrivere codice invece di un modulo principale. Questo ci consentirà di utilizzare gli eventi del foglio di lavoro.

Se i dati di origine e le tabelle pivot si trovano in fogli diversi, scriveremo il codice VBA per modificare lorigine dati della tabella pivot nelloggetto di lavoro che contiene i dati di origine ( non che contiene tabelle pivot).

Premi CTRL + F11 per aprire leditor di VB. Ora vai a Esplora progetto e trova il foglio che contiene i dati di origine. Fare doppio clic su di esso.

Si aprirà una nuova area di codifica. Potresti non vedere alcun cambiamento ma ora hai accesso agli eventi del foglio di lavoro.

Fai clic sul menu a discesa a sinistra e seleziona il foglio di lavoro. Dal menu a discesa a sinistra, seleziona Disattiva. Vedrai un sottotitolo vuoto scritto sul nome dellarea del codice worksheet_deativate. Il nostro codice per modificare dinamicamente i dati di origine e aggiornare la tabella pivot andrà in questo blocco di codice. Questo codice verrà eseguito ogni volta che passerai dal foglio dati a qualsiasi altro foglio. Puoi leggere tutti gli eventi del foglio di lavoro qui.

Ora siamo pronti per implementare il codice.

Codice sorgente per aggiornare dinamicamente tutte le tabelle pivot nella cartella di lavoro con un nuovo intervallo

Per spiegare come funziona, ho una cartella di lavoro. Questa cartella di lavoro contiene tre fogli. Sheet1 contiene i dati di origine che possono cambiare. Sheet2 e Sheet3 contengono tabelle pivot che dipendono dai dati di origine di sheet2.

Ora ho scritto questo codice nellarea di codifica di sheet1. Sto utilizzando levento Worksheet_Deactivate, in modo che questo codice venga eseguito per aggiornare il tabella pivot ogni volta che si passa dal foglio dati di origine.

Se si dispone di una cartella di lavoro simile, è possibile copiare direttamente questi dati. Ho spiegato che questo codice funziona di seguito in modo da poterlo modificare secondo le proprie esigenze.

Puoi vedere leffetto di questo codice nella gif di seguito.

Come cambia automaticamente questo codice dati di origine e aggiornare le tabelle pivot?

Prima di tutto abbiamo utilizzato un evento worksheet_deactivate. Questo evento si attiva solo quando il foglio contenente il codice viene cambiato o disattivato. Quindi è così che il codice viene eseguito automaticamente.

Per ottenere dinamicamente lintera tabella come intervallo di dati, determiniamo lultima riga e lultima colonna.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Column

Usando questi due numeri definiamo source_data. Siamo certi che lintervallo di dati di origine inizierà sempre da A1. Puoi definire il tuo riferimento di cella iniziale.

Imposta source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Ora abbiamo i dati di origine che sono dinamico. Dobbiamo solo usarlo nella tabella pivot.

Poiché non sappiamo quante tabelle pivot conterrà una cartella di lavoro alla volta, eseguiremo un ciclo attraverso ogni foglio e le tabelle pivot di ogni foglio. Quindi che non sia rimasta alcuna tabella pivot. Per questo utilizziamo cicli for nidificati.

Per ogni ws in ThisWorkbook.Worksheets

Per ogni pt in ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Punto successivo

Prossimo ws

Il primo ciclo scorre in ogni foglio. Il secondo ciclo itera su ogni tabella pivot in un foglio.

Le tabelle pivot vengono assegnate alla variabile pt. Usiamo il metodo ChangePivotCache delloggetto pt. Creiamo dinamicamente una cache pivot utilizzando il metodo ThisWorkbook.PivotCaches.Create

. Questo metodo accetta due variabili SourceType e SourceData. Come tipo di origine dichiariamo xlDatabase e come SourceData passiamo lintervallo source_data che abbiamo calcolato in precedenza.

E che i s it. Abbiamo le nostre tabelle pivot automatizzate. Questo aggiornerà automaticamente tutte le tabelle pivot nella cartella di lavoro.

Quindi sì ragazzi, è così che puoi modificare dinamicamente gli intervalli di origini dati di tutte le tabelle pivot in una cartella di lavoro in Excel. Spero di essere stato abbastanza esplicativo. Se hai domande su questo articolo, fammelo sapere nella sezione commenti qui sotto.

Come aggiornare dinamicamente lintervallo di origini dati delle tabelle pivot in Excel: per modificare dinamicamente lintervallo di dati di origine delle tabelle pivot, utilizziamo le cache pivot. Queste poche righe possono aggiornare dinamicamente qualsiasi tabella pivot modificando lintervallo di dati di origine.

Come aggiornare automaticamente le tabelle pivot utilizzando VBA: per aggiornare automaticamente le tabelle pivot è possibile utilizzare eventi VBA. Usa questa semplice riga di codice per aggiornare automaticamente la tua tabella pivot. Puoi utilizzare uno dei 3 metodi di aggiornamento automatico delle tabelle pivot.

Esegui macro se qualsiasi modifica apportata sul foglio in un intervallo specificato: nelle tue pratiche VBA, avresti la necessità di eseguire macro quando un determinato intervallo o cambiamenti cellulari. In tal caso, per eseguire macro quando viene apportata una modifica a un intervallo di destinazione, utilizziamo levento di modifica.

Esegui macro quando viene apportata una modifica sul foglio | Quindi, per eseguire la macro ogni volta che il foglio viene aggiornato, utilizziamo gli eventi del foglio di lavoro di VBA.

Il codice VBA più semplice per evidenziare la riga e la colonna correnti utilizzando | Usa questo piccolo frammento VBA per evidenziare la riga e la colonna correnti del foglio.

Gli eventi del foglio di lavoro in Excel VBA | Gli eventi del foglio di lavoro sono davvero utili quando desideri che le tue macro vengano eseguite quando si verifica un evento specifico sul foglio.

Articoli popolari:

50 scorciatoie Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel. La funzione CERCA.VERT in Excel | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da diversi intervalli e fogli.

CONTA.SE 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 dashboard.

Come utilizzare la funzione SUMIF in Excel | Questa è unaltra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori a condizioni specifiche.