Om ha centinaia di fogli di lavoro con nomi diversi tutti nella stessa cartella di lavoro. La struttura di ogni foglio di lavoro è essenzialmente la stessa. Vuole copiare un intervallo fisso (A146: O146) da ogni foglio di lavoro a un nuovo foglio di lavoro, una riga dopo l’altra.

Ci sono alcuni modi in cui puoi farlo. Per i miei soldi, una semplice macro è la soluzione migliore. Prima di entrare nell’approccio basato su macro, tuttavia, c’è un modo per farlo usando le formule. Un modo piuttosto unico si basa sulla definizione di una formula in Name Manager. Segui questi passaggi per iniziare:

  1. Visualizza la scheda Formule della barra multifunzione.

  2. Nel gruppo Nomi definiti, fare clic sullo strumento Gestione nomi. Excel visualizza la finestra di dialogo Gestione nomi.

  3. Fare clic sul pulsante Nuovo. Excel visualizza la finestra di dialogo Nuovo nome. (Vedi figura 1.)

  4. Nel campo Nome, inserisci il nome ListSheets (nota che questa è una singola parola, senza spazi).

  5. Nel campo Si riferisce a, immettere la seguente formula:

  6. Fare clic sul pulsante OK per completare la creazione dell’intervallo denominato. Il nuovo intervallo dovrebbe essere visualizzato nella finestra di dialogo Gestione nomi.

  7. Fare clic sul pulsante Chiudi per chiudere la finestra di dialogo Gestione nomi.

Con la formula definita in questo modo, puoi quindi andare al foglio di lavoro su cui vuoi unire tutti quegli intervalli. (Diciamo che il nome di questo foglio di lavoro è “Riepilogo”.) Suggerisco caldamente di assicurarti che questo foglio di lavoro sia l’ultimo nella tua cartella di lavoro. Nel foglio di lavoro Riepilogo, inserisci la seguente formula nella colonna A di qualsiasi riga:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

Copia la formula verso il basso, tuttavia sono necessarie molte righe per rappresentare tutti i fogli di lavoro. In altre parole, se hai 25 fogli di lavoro (senza contare il foglio di lavoro Riepilogo), copia la formula verso il basso di 24 righe. Contando l’originale, ora dovresti visualizzare la formula su un totale di 25 righe.

Una nota a margine qui: la formula ListSheets, quella che hai definito in Name Manager, restituisce un array di nomi di fogli di lavoro. La funzione RIGHE viene utilizzata per determinare quale elemento di tale matrice viene restituito tramite la funzione INDICE. Se il tuo foglio di lavoro Riepilogo non è l’ultimo nella tua cartella di lavoro, può essere facilmente restituito da ListSheets e finirai per estrarre valori da esso. Questo è indubbiamente qualcosa che non vuoi fare, motivo per cui ho suggerito di assicurarmi che il Riepilogo fosse l’ultimo foglio di lavoro nella cartella di lavoro.

Ora copia semplicemente le formule dalla colonna A a destra in modo da averle fino alla colonna O. Il risultato è che avrai i valori da A146: O146 nelle celle contenenti le formule.

In precedenza ho detto che penso che un approccio basato su macro sia la soluzione migliore.

Ecco una breve macro che dimostra perché questo è il caso.

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

Notare che ci sono due variabili (sNewName e sRange) impostate all’inizio della macro. Rappresentano il nome che si desidera utilizzare per il nuovo foglio di lavoro di riepilogo creato dalla macro e l’intervallo di celle che si desidera copiare da ciascun foglio di lavoro.

La macro quindi rende attivo il primo foglio di lavoro nella cartella di lavoro e aggiunge un nuovo foglio di lavoro da utilizzare per il riepilogo. A questo foglio di lavoro viene assegnato il nome specificato nella variabile sNewName. La macro quindi esegue un ciclo controllando ciascuno degli altri fogli di lavoro. Finché il foglio di lavoro non è quello di riepilogo, il nome del foglio di lavoro viene inserito nella colonna A del foglio di riepilogo e l’intervallo specificato nella variabile sRange (A146: O146) viene copiato nel foglio di lavoro di riepilogo a partire dalla colonna B .

L’approccio macro è veloce e facile. Inoltre, se hai bisogno di rifare il tuo foglio di riepilogo, elimina quello vecchio e riesegui la macro.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (9812) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.