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:

  • Option Compare Text

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