Aggiungere nuovi fogli, nominarli con nomi
presenti in un elenco, trasferimento dati correlati al nome su foglio stesso
nome.
-
pagina vista: volte
Requisiti:
conoscenza dei cicli For...Next
L'argomento di
questo articolo è già descritto nel titolo pagina: disponendo di una tabella
contenente dati, su un foglio di lavoro, desideriamo creare nuovi
fogli (se non presenti) nominandoli con i nomi presenti nella prima colonna
della tabella, e in ogni nuovo foglio, trasferire, meglio "copiare", i dati
correlati ai nomi di cui si è creato il foglio. Vediamo una tabella
d'esempio.
In pratica
vogliamo raggruppare tutti i dati di un nominativo riunendoli in un foglio
con lo stesso nominativo. Quindi otterremo, sul foglio "pippo", ad esempio,
le 5 righe di dati relativi al nome "pippo", (lo stesso sarà per ogni altro
foglio aggiunto: ogni nome sarà riunito nel foglio relativo) così:
Vogliamo inoltre, per ogni nuovo foglio, copiare le intestazioni di colonna
(campi in giallo), in modo da riformare l'inizio elenco.
Considerazioni.
La necessità di
creare tanti fogli quanti sono i nominativi presenti, partendo da un elenco
come la tabella di partenza, presuppone che la decisione di suddividere i
dati in fogli personalizzati, sia successiva alla creazione della tabella, e
la si faccia una sola volta.
Direi che se si
volevano registrare dati su fogli personalizzati (intendo come nome foglio),
conveniva creare un nuovo foglio all'atto dell'immissione di dati, e poi
successivamente, se aggiungevamo dati per lo stesso nominativo, usavamo il
foglio già creato, senza fare una tabella unica da cui estrarre i dati.
Sarebbe stato sufficiente usare una maschera unica di introduzione dati (una
UserForm) che all'atto della registrazione, controllasse se esisteva il
foglio, lo creasse nel caso mancasse, e vi registrasse i dati che avremmo
scritto in tante TextBox presenti sulla UserForm. Di esempi di questo tipo
ne avrete letti diversi, almeno su questi siti. In genere la programmazione,
presuppone di organizzare il lavoro in funzione di ciò che vogliamo
ottenere, evitando se possibile, ripensamenti che ci portano a complicazioni
e rigiri di nuove istruzioni.
Ma, in
contrapposizione a quanto affermato sopra, noi possiamo aver deciso di
tenere una tabella complessiva, da aggiornare con nuovi inserimenti, in modo
da ottenere un riepilogo generale dei movimenti. Necessità più che giusta,
ma che pone un problemino facilmente risolvibile: se lanceremo più volte le
istruzioni per creare nuovi fogli o aggiungere dati a fogli già esistenti,
per evitare di copiare dati già trasferiti, abbiamo bisogno di "segnale" (un
flag) che ci dica se una riga di dati è già stata copiata: useremo quindi
un'altra colonna (nell'esempio la F ) dove scrivere qualcosa, e noi faremo
aggiungere un'asterisco ( * ) che indicherà alle istruzioni che quella riga
è già stata copiata.
Questo ci consente
di usare la tabella, aggiungendo dati di nominativi, (nuovi o già presenti)
e di ottenere, rilanciando le istruzioni, l'aggiornamento dei dati sui fogli
relativi al nominativo letto, o di creare nuovi fogli.
Attenzione solo al
numero di fogli che si andranno creando: non esiste un limite sul numero di
fogli che una cartella può contenere, ma esiste un limite rappresentato
dalla potenza (RAM e CPU) del nostro computer: un numero elevato di fogli
può mettere in crisi il computer e provocare blocchi.
Procedure.
Premesso che
possiamo realizzare tutta la procedura come unica Sub, suggerisco di
dividere il progetto in due procedure: la prima che controlli i nomi dei
fogli e crei nuovi fogli per i nuovi nominativi; la seconda che copi i dati
relativi ai nominativi nei fogli stesso nome.
Vediamo quindi
come organizzare il nostro lavoro: abbiamo bisogno di:
-
useremo un ciclo
For..Next per scorrere tutta la colonna contenente i nomi, e per ogni nome
letto, controllare se esiste il foglio con lo stesso nome; se non esiste lo
creiamo (metodo Add), lo spostiamo alla fine dei fogli già presenti, e
copiamo nel nuovo foglio le intestazioni di campo
(Nome,pere,mele,banane,pesche) nella riga 1 (o altra che deciderete).
Per la copia dei dati nelle intestazioni di colonna nel nuovo foglio, usiamo
il metodo Select + Copy, ed il metodo Paste destination (ecc.ecc.).
-
Per la verifica
dell'esistenza del foglio, usiamo un ciclo interno For Each next collegato
ad un flag inizializzato a zero: se il nome che stiamo cercando esiste,
poniamo il flag ad 1; se il flag rimane a zero (quindi nome foglio non
presente), creiamo il nuovo foglio col nome letto nella colonna A della
tabella.
Sub GeneraFogli()
Dim URO As Integer 'dichiarazioni
variabili
Dim Clic
Dim Nome As String
Application.ScreenUpdating = False 'evitiamo
il saltellamento a schermo
URO = Sheets(1).[A65536].End(xlUp).Row
'con URO otteniamo il numero dell'ultima riga
occupata, colonna A Foglio 1
For N = 2 To URO
'iniziamo un ciclo che scorre le celle dalla
riga 2 fino all'ultima riga occupata
Clic = 0
'impostamo il flag "Clic" a zero ad
ogni ciclo
Nome = Sheets(1).Cells(N, 1) 'con
"Nome" prendiamo il nominativo ora letto nella cella riga N, colonna 1
For Each ws In Worksheets 'quindi
iniziamo un ciclo che scorra tutti i fogli presenti in cartella
If ws.Name = Nome Then
'se un nome del foglio letto è uguale al nominativo ora in "Nome"
Clic = 1
'impostiamo il flag a 1
End If
Next
If Clic = 0 Then 'se
NON avremo trovato fogli corrispondenti a "Nome" il flag è a zero e
quindi
Set NuovoFoglio = Worksheets.Add
'aggiungiamo un nuovo foglio
NuovoFoglio.Name = Nome
'assegniamo al nuovo foglio il nome
presente nella variabile "Nome"
Sheets(Nome).Move After:=Sheets(Sheets.Count)
'e spostiamo il nuovo foglio alla fine dei
fogli presenti
Sheets(1).Activate
'ritorniamo sul foglio1 (l'ultimo foglio
creato sarebbe il foglio attivo)
Sheets(1).Range("A1:E1").Copy
'copiamo il Range delle intestazioni di
campo
ActiveSheet.Paste Destination:=Sheets(Nome).Range("A1:E1")
'e incolliamo le intestazioni nello stesso Range
sul nuovo foglio
Application.CutCopyMode = False
'eliminiamo il tratteggio intorno alle celle
copiate, foglio1
End If
Next
End Sub |
Passiamo alla
seconda routine, quella che dovrà copiare i dati, riunendo tutti i dati di
uno stesso nominativo, nel foglio con lo stesso nome. i fogli ce li ha
predisposti la precedente routine.
-
Anche qui avremo
bisogno di scorrere tutti i nominativi presenti con un ciclo For..Next , e
per la copia dei dati sul foglio destino usiamo un'altro sistema, giusto per
abituarci a lavorare con procedure diverse: impostiamo (con l'istruzione
Set) la variabile "Area" con l'intervallo delle colonne da 1 alla 5, della
riga in quel momento letta dal ciclo.
-
Abbiamo inoltre
bisogno, per evitare di sovrascrivere i dati, di cercare sul foglio destino,
la prima cella libera nella colonna A, iniziando dalla riga 1 (rigadest),
per questo usiamo un ciclo veloce (While..Wend), e trovata la cella libera,
ci copiamo il contenuto della variabile "Area".
-
inoltre, per
"marcare" i dati, sul foglio1, come dati già copiati, inseriamo nella stessa
riga "R", colonna F (la 6) un'asterisco (*) . Quando inizia il ciclo, si
controlla che non esista l'asterisco, se esiste, si passa alla cella
successiva. Questo controllo ci consente di aggiungere nuovi dati in
tabella, dati che solo loro saranno copiati.
Sub TrasferisciDati()
Dim URO As Integer
Dim Nome As String
Dim R As Integer
URO = Sheets(1).[A65536].End(xlUp).Row
For R = 2 To URO
If Cells(R, 6) = "*" Then GoTo 10
'se la cella riga R, colonna 6, contiene
l'asterisco, si passa alla cella successiva
Nome = Cells(R, 1)
'prendiamo con "Nome" il nominativo nella
cella ora letta
Set Area = Sheets(1).Range(Cells(R, 1), Cells(R, 5))
'impostiamo con Area l'intervallo da
copiare nel foglio stesso nome
rigadest = 1
'impostiamo a 1 il numero di riga per il
foglio destino
While Sheets(Nome).Cells(rigadest, 1) <> ""
'iniziamo il ciclo While che cercherà sul foglio
uguale a "Nome" la prima cella libera
rigadest = rigadest + 1
Wend
Area.Copy (Sheets(Nome).Cells(rigadest, 1))
'trovata la cella libera, vi si copia il contenuto di Area (5
colonne); provvede excel 'ad occupare tante celle a destra della cella
libera, quante sono le celle memorizzate con Area.
ActiveSheet.Cells(R, 6) = "*"
'e sul foglio attivo (foglio1) si pone l'*
nella riga R, colonna F
10:
Next
End Sub |
Avvertenze: sappiamo che il vba è CaseSensitive e lavorando
con nominativi che possono essere scritti a volte non nella stessa forma per
quanto concerne maiuscole/minuscole, è opportuno posizionare sul (o sui)
moduli contenenti la/le macro, nella sezione Generale - Dichiarazioni,
la seguente istruzione:
eviteremo di
ritrovarci con duplicati di fogli o mancato riconoscimento di un nominativo,
solo perchè avremo scritto una volta PIPPO e un'altra volta Pippo.
Cos'altro dire: se
desiderassimo avere i fogli ordinati alfabeticamente per nome foglio,
anzichè provvedere con una routine che provveda al loro ordinamento per
ordine alfabetico, converrà prima ordinare alfabeticamente tutta la tabella
contenente i dati (compreso la colonna F, quella per gli asterischi), e poi
lanciare le due istruzioni: i fogli creati sarebbero automaticamente posti
in ordine alfabetico.
Buon lavoro.
prelevato sul sito
www.ennius.altervista.org
|