Lavorare con fogli diversi

In linea di massima le operazioni che si possono compiere sono le stesse di quelle che siamo abituati a compiere sul foglio attivo: selezioni ed individuazione di intervalli, lettura e scrittura di valori, ecc.

C'è solo bisogno di allargare le nostre vedute. Basta indicare al codice che, invece di lavorare sulle celle del foglio attivo, abbiamo intenzione di lavorare su celle di altri fogli.

Lavorando su oggetti diversi quali possono essere due differenti fogli per trattare (leggere o scrivere) valori che si trovano in un foglio o in un altro in linea di massima occorre specificare il foglio sul quale il valore è residente o sul quale vogliamo scrivere il valore. Non è sufficiente un generico:

Range("D5") = Range("B5") ma a volte è necessario specificare sia il foglio di destinazione che il foglio di origine come nell'esempio:

Sheets("Foglio1").Range("A9") = Sheets("Foglio3").Range("A1").

Per chiarirci le idee io penso che nell'uso del VBA dobbiamo acquisire un po' una mentalità militare per comprendere come in questo ambiente regna la gerarchia.

Questa gerarchia di oggetti viene a formare come una piramide alla cui cima sta l'oggetto da cui hanno origine tutti gli altri oggetti intermedi che a loro volta contengono altri oggetti via via più piccoli e più numerosi.

E' così che, partendo dall'oggetto Application, che rappresenta il padre dell'intera applicazione Excel, arriviamo, passando attraverso oggetti intermedi (Workbooks, Sheets, ecc), verso gli oggetti di ordine inferiore (Range, Cells per scendere fino a Font, Interior, ecc).

Nelle nostre istruzioni occorre solo porre attenzione a fare i giusti riferimenti ai giusti oggetti. Tanto per rimanere nel tema diciamo subito che per lavorare con celle di fogli diversi, le nostre azioni possono essere eseguite

attivando e selezionando i vari fogli e le varie celle di volta in volta prima di eseguire qualsiasi operazione
oppure eseguire il lavoro su altri fogli e celle restando sul foglio attivo.

Queste possono essere le situazioni in cui possiamo venire a trovarci:

il foglio attivo non è nessuno di quelli su cui intendiamo svolgere l'azione
il foglio attivo è quello di destinazione
il foglio attivo è quello di origine

Fatte queste premesse possiamo passare ad esaminare le istruzioni che di volta in volta occorre utilizzare.

Per copiare i valori da una cella di un foglio ad un'altra cella di un altro foglio:

nel primo caso l'istruzione completa è obbligatoria:
Sheets("Foglio1").Range("A9") = Sheets("Foglio3").Range("A1")
nel secondo caso l'istruzione può essere semplificata omettendo il riferimento al foglio di destinazione che è quello attivo:
Range("A9") = Sheets("Foglio3").Range("A1")
nel terzo caso verrà omesso il riferimento al foglio di origine che è attivo:
Sheets("Foglio1").Range("A9") = Range("A1")

Per selezionare una cella di un altro foglio:

Per compiere questa azione saremmo tentati a scrivere una cosa del genere:

Sheets("Foglio2").Range("A1").Select

Purtroppo l'istruzione genera un errore se il Foglio2 non è già attivo (ma in questo caso il riferimento al foglio sarebbe inutile). Il motivo dell'errore sollevato da questa istruzione è che non si può selezionare una cella in un foglio che non sia quello attivo e che l'istruzione non riesce ad eseguire contemporaneamente le due azioni: attivare il foglio e selezionare la cella indicata.

Vediamo subito un esempio destinato a fallire:

Sub seleziona1()
Dim N As Integer
For N = 1 To Worksheets.Count
Sheets(N).Range("C10").Select ' questa istruzione creerà un errore se il foglio non è quello attivo
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

A questa istruzione possiamo (dobbiamo) sostituire una delle seguenti istruzioni:

Application.Goto Sheets("Foglio2").Range("A1")
Sheets("Foglio2").Activate
Sheets("Foglio2").Range("A1").Select

Vediamo gli esempi corretti per ciclare tra i fogli e selezionare le celle usando le due sintassi:

Sub seleziona_con_Goto()
Dim N As Integer
For N = 1 To Worksheets.Count
'Sheets(N).Range("C10").Select ' questa è stata sostituita con la seguente riga
Application.Goto Sheets(N).Range("C10")
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

oppure l'altra:

Sub seleziona_con_doppia_azione()
Dim N As Integer
For N = 1 To Worksheets.Count
'Sheets(N).Range("C10").Select ' questa è stata sostituita con la seguente riga
Sheets(N).Activate
Sheets(N).Range("C10").Select
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

Lavorare a distanza e senza alcuna selezione

In genere è inutile effettuare selezioni. Sarà sufficiente lavorare a distanza, cioè lavorare su ogni foglio senza andarci fisicamente, e potremo cambiare la routine come mostrato qui di seguito:

Es. 1a Modificare una cella per ogni foglio senza effettuare alcuna selezione

Sub senza_Selezione()
Dim N As Integer
For N = 1 To Worksheets.Count
If Sheets(N).Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

L'uso dell'istruzione With

In tutti i casi in cui occorre compiere molte azioni su un singolo oggetto, per semplificare le istruzioni, è possibile usare l'istruzione With. L'istruzione With può anche essere nidificata, ma l'istruzione With più interna, pur riferendosi ad un oggetto diverso da quello più esterno, dovrà comunque riferirsi ad un oggetto gerarchicamente inferiore a quello a cui il primo With si riferisce.

With Sheets("Foglio1")
With .Range("K1")
With .Font

In questa serie di With ci riferiamo all'oggetto Font dell'oggetto Range dell'oggetto Sheets rispettandone rigorosamente la scala gerarchica.

In una serie di With nidificati non ci si potrà riferire ad un altro oggetto gerarchicamente uguale o superiore a quello puntato dal With più esterno. Se col primo With ci riferiamo ad un determinato Foglio, col secondo With dovremo riferirci ad una cella di quel foglio e non ad un altro foglio o una cella di un altro foglio. Se col secondo With vogliamo riferirci ad un'altra cella dello stesso foglio, dobbiamo prima chiudere il precedente riferimento alla cella e riaprirne un altro che si riferisca ad un'altra cella.

With Sheets("Foglio1")
With .Range("K1")
...........
End With
With .Range("K5")
...........
End With
End With

Gli oggetti dipendenti dall'oggetto puntato da With saranno contraddistinti da un punto (.) usato come prefisso:

With Sheets("Foglio1")
.Range("K1") = Sheets("Foglio2").Range("A11")
in questo caso Range("K1") è un oggetto dipendente del Foglio1 ed è preceduto dal punto, il resto dell'istruzione osserva la normale sintassi legata all'indicazione di intervalli del Foglio2
With Sheets("Foglio1")
.Range("K1") = Range("A11")
anche in questo caso Range("K1") è un oggetto dipendente del Foglio1 ed è preceduto da un punto, ma Range("A11") che non è preceduto da alcun punto o riferimenti a fogli di appartenenza si riferisce al foglio attivo che potrebbe essere un qualsiasi foglio della cartella

Se il nostro lavoro dovrà svolgersi su due differenti fogli magari diversi da quello attivo, anche se è lecito pensare di usare l'istruzione With per semplificare le nostre azioni, è tuttavia necessario decidere quale dei fogli e relative celle deve essere indicato da With e quale trattare senza With.

Se scriviamo: With Sheets("Foglio1") con un successivo With nidificato nel primo dovremo riferirci ad una cella dello stesso Foglio1: With .Range("K1") oppure: With .Range("K1").Font ma non possiamo riferirci a celle di un altro foglio. Vediamo un esempio pratico:

Di seguito propongo alcuni esempi per chiarire i concetti sin qui esposti.

Questo esempio mostra una ulteriore espansione dell'Esempio visto sopra

Sub ControllaSeVuota
For N = 1 To Worksheets.Count
With Sheets(N)
If .Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
End With
Next
End Sub

Questo che segue è simile al precedente

With Sheets("Foglio1")
If .Range("C10") = "" Then
.Range("C10") = Sheets("Vendite").Range("K1")
End If
End With

Con questo esempio spazzoliamo tutti i fogli di una cartella per controllare se la cella C10 è vuota

Sub ControllaSeVuota()
Dim N
For N = 1 To Worksheets.Count
With Sheets(N)
If .Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
End With
Next
End Sub

Nel seguente esempio il foglio attivo è uno diverso dal Foglio1 e senza spostarci da questo eseguiamo alcune azioni sul Foglio1. Uso tre With nidificati ognuno dei quali si riferisce a vari oggetti gerarchicamente via via più bassi:

Sheets ---> Range ---> Font
Sheets ---> Range ---> Interior

Per entrare nell'Interior dell'oggetto Range, dobbiamo prima uscire da Font dello stesso oggetto Range.

Nello scrivere la routine dobbiamo porre la massima attenzione al punto ( . ) da scrivere prima delle proprietà dell'oggetto a cui ci riferiamo:

Sub With_Nidificati()
With Sheets("Foglio1") ' qui puntiamo al foglio1
MsgBox .Name ' ne leggiamo il nome
.Range("K2") = .Name ' e lo scriviamo nella cella K2
.Tab.ColorIndex = 3 ' quindi coloriamo la relativa scheda (Tab)
With .Range("K2") ' ci spostiamo ora sulla cella K2 per formattarla
With .Font
.Name = "Arial"
.FontStyle = "Grassetto"
.Size = 11
.Strikethrough = True
.ColorIndex = 3
End With
With .Interior
.ColorIndex = 40
.PatternColorIndex = xlAutomatic
End With
End With
.Tab.ColorIndex = xlColorIndexNone ' qui togliamo il colore alla scheda del foglio 2
.Range("A2") = .Range("K2").Value ' e copiamo in A2 quel che avevamo scritto in K2
End With
End Sub

Con questo codice siamo in grado di lavorare sul Foglio1 anche se ci troviamo su un altro foglio.

Volendo fare a meno delle istruzioni With il nostro codice si leggerebbe pressappoco così:

Sheets("Foglio1").Range("K2").Font.Name = "Arial"
Sheets("Foglio1").Range("K2").Font.FontStyle = "Grassetto"
Sheets("Foglio1").Range("K2").Font.Size = 11
Sheets("Foglio1").Range("K2").Interior.ColorIndex = 40 ecc

Estrarre dati univoci da una tabella

In questo esempio, da una tabella globale, vogliamo estrarre, di volta in volta, i vari elementi e deporli, senza ripetizioni, in un altro foglio e segnalarne tutte le occorrenze.

Cod Art	Operatore	Prodotto	Confez.	Pezzi venduti
35031	Andrea	Tonno	scatole	181
84040	Massimo	Coca Cola	bottiglie	53
67030	Maurizio	Maccheroni	confezione	115
48012	Silvano	Maionese	vasetti	20
10060	Alessio	Tonno	scatole	107
20070	Giovanni	Maionese	vasetti	127
53040	Giuseppe	Maccheroni	confezione	438
61040	Oreste	Mostarda	vasetti	425
22050	Massimo	Alici	scatole	402
53021	Alessio	Maionese	vasetti	182
09071	Giovanni	Pane	confezione	130
65020	Oreste	Maccheroni	confezione	220
15024	Andrea	Tonno	scatole	355
24021	Silvano	Pane	confezione	170
04010	Silvano	Coca Cola	bottiglie	128
07020	Maurizio	Alici	scatole	119
21049	Giuseppe	Mostarda	vasetti	95
20081	Silvano	Maccheroni	confezione	225
23030	Massimo	Alici	scatole	15

ottenendo, a seconda della scelta effettuata, degli elenchi così composti:

Operatore	Occorrenze	Prodotto.	Occorrenze	Confez	Occorrenze
Andrea	2	Tonno	3	scatole	6
Massimo	3	Coca Cola	2	bottiglie	2
Maurizio	2	Maccheroni	4	confezione	6
Silvano	4	Maionese	3	vasetti	5
Alessio	2	Mostarda	2
Giovanni	2	Alici	3
Giuseppe	2	Pane	2
Oreste	2

Con la routine che verrà presentata tra poco si potrà agire su due fogli da un qualsiasi foglio della cartella di lavoro. Ossia:

a prescindere da quale sia il foglio attivo preleveremo i dati dal foglio denominato "Vendite" e depositati, dopo l'elaborazione, nel foglio denominato "Riepilogo".

Unico vincolo è quello di scrivere nella cella "I1" la colonna della tabella da esaminare:

es: 1 per la colonna 1, 2 per la colonna 2, ecc.

Le azioni che compieremo nel codice saranno queste:

determiniamo l'intervallo di origine con le ormai note istruzioni:

With Sheets("Vendite").Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Col = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Col)
End With

dopo di questo si legge il contenuto della cella "I1" e se ne verifica il valore:

Colonna = Worksheets("Vendite").Range("I1")
If Colonna = "" Then
MsgBox "scrivere il numero di colonne nelle cella I1 del foglio Vendite"
Exit Sub
End If
If Colonna < 1 Or Colonna > Col Then
MsgBox "Nella cella I1 del foglio Vendite scrivere solo valori numerici tra 1 e " & Col
Exit Sub
End If

dopo di questo si passa a lavorare nell'intervallo precedentemente determinato per creare un elenco univoco di tutti gli elementi della colonna in esame usando una Collection:

On Error Resume Next
For R = 1 To Righe
Unici.Add Intervallo(R, Colonna).Value, CStr(Intervallo(R, Colonna).Value)
Next
On Error GoTo 0

quindi si conclude trasferendo i dati trovati nel foglio di destinazione.

Con With Sheets("Riepilogo") si punterà al foglio denominato "Riepilogo"

dopo aver contato, nel foglio di origine le occorrenze di ciascun elemento contenuto nella collection si aggiorna la nuova tabella fel foglio di destinazione denominato "Riepilogo" (notate come ci si riferisce alle celle del foglio "Riepilogo" anteponendo un punto ( . ) a queste celle.

Tot = Unici.Count: R2 = 1
With Sheets("Riepilogo")
.Range("A1").CurrentRegion.ClearContents
.Range("A1") = Sheets("Vendite").Cells(1, Colonna)
For R = 1 To Tot
Conta = 0
Valore = Unici(R)
For R1 = 1 To Righe
If Intervallo(R1, Colonna) = Valore Then
Conta = Conta + 1
End If
Next
R2 = R2 + 1
.Cells(R2, 1) = Valore
.Cells(R2, 2) = Conta
Next
End With

Questo che segue è il codice completo:

Sub CopiaInAltroFoglio()
Dim Intervallo As Range
Dim Unici As New Collection
Dim Righe, R, R1, R2, Col, Colonna
Dim Tot, Conta, Valore
' qui viene creato l'intervallo che contiene la tabella di origine
With Sheets("Vendite").Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Col = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Col)
End With
' ora viene controllato il numero di colonna che dovrebbe essere nella cella "I1" del foglio "Vendite"
Colonna = Worksheets("Vendite").Range("I1")
If Colonna = "" Then
MsgBox "scrivere il numero di colonne nelle cella I1 del foglio Vendite"
Exit Sub
End If
If Colonna < 1 Or Colonna > Col Then
MsgBox "Nella cella I1 del foglio Vendite scrivere solo valori numerici tra 1 e " & Col
Exit Sub
End If
' ora viene creata una nuova Collection
On Error Resume Next
For R = 1 To Righe
Unici.Add Intervallo(R, Colonna).Value, CStr(Intervallo(R, Colonna).Value)
Next
On Error GoTo 0
If Unici Is Nothing Then
MsgBox "collezione non creata"
Exit Sub
End If
' finalmente si passa a processare la colonna dell'intervallo di origine
' ed infine gli elaborati vengono copiati nel foglio di destinazione
Tot = Unici.Count: R2 = 1
With Sheets("Riepilogo")
.Range("A1").CurrentRegion.ClearContents
.Range("A1") = Sheets("Vendite").Cells(1, Colonna)
For R = 1 To Tot
Conta = 0
Valore = Unici(R)
For R1 = 1 To Righe
If Intervallo(R1, Colonna) = Valore Then
Conta = Conta + 1
End If
Next
R2 = R2 + 1
.Cells(R2, 1) = Valore
.Cells(R2, 2) = Conta
Next
End With
End Sub

Se qualcosa ancora non è chiaro, penso che potremmo tornare sull'argomento.