Cartella lavoro: creare dei collegamenti dinamici tra i fogli.

Question

Ciao a tutti,

ho un problema col creare "finestre/fogli excel" all'interno di un foglio di lavoro della medesima cartella di lavoro.

Spiego il problema con un esempio banale. Ho una cartella di lavoro, con 3 fogli:

1. Il primo foglio raccoglie dei dati "globali". E' in realtà più una sorta di "cartiglio" per la stampa del documento finale; al suo interno ho il riferimento agli altri due fogli (numero 2 e 3) (=FoglioX!YN)
2. Sui fogli 2 e 3 raccolgo il brutto del lavoro... tante righe di dati in continuo aggiornamento.

Il problema è proprio questo aggiornamento.

Sul foglio 1 Globale ho due range di righe consecutive che racchiudono rispettivamente le informazione dei fogli 2 e 3. Purtroppo però non so come gestire dinamicamente il problema, cioè: supponiamo di avere alla data di oggi il seguente stato:

Foglio 2 -> 10 righe di dati

Foglio 3 -> 20 righe di dati

Il giorno dopo:

Foglio 2 -> 8 righe di dati (2 righe sono sparite perché sono informazioni di cui non ho più bisogno nello stampare il documento globale Foglio 1)

Foglio 3 -> 20 righe di dati

Mi ritrovo dunque 2 righe di interspazio tra le due finestre consecutive.

Sono così costretto ad eliminare le due righe.

Poi magari il giorno dopo ancora mi ritrovo con:

Foglio 2 -> 15 righe di dati

Foglio 3 -> 10 righe di dati

Allora devo inserire 7 nuove righe nel Foglio 1 per crearmi lo spazio, ricreare il riferimento per queste nuove 7 righe ai Fogli 2 e 3 e tutto torna... ma è un lavoro sinceramente evitabile? Ovvero, esiste un modo per gestire delle fantomatiche finestre DINAMICHE che si allungano dinamicamente in funzione del numero di righe che i 2 fogli hanno? Ovvero un sistema che riconosca DOVE i dati dei fogli 2 e 3 finiscono e quindi accorciare od allungare le finestre dinamicamente senza che sia costretto ogni volta ad aggiungere io manualmente nuove righe di riferimenti o eliminarle?

Grazie a tutti per l'aiuto e spero di essere stato esaustivo e chiaro.

Michele.

Answer 1

Non ho però capito se il Range destinato a Foglio2 e Foglio3 sul Foglio1 può cambiare, cioè se A4:H12 e A14:H30 sono fissi sul Foglio1 o se possono/devono espandersi/contrarsi.

     

Si, il Range destinato al Foglio 1 può cambiare cioè A4:H12 e A14:H30non sono fissi ma devono appunto espandersi/contrarsi (solo per il numero di righe però).

Ho provato tutte e due le macro: in quella di elianof, la riga 14 rimane fissa quando invece dovrebbe adattarsi alla fine del range che desume dal Foglio 2 (ma il resto è perfetto :-)), mentre nella macro di Gamberini la riga 14 viene "sovrascritta" qualora il range di dati nel foglio 2 sia superiore alle 9 righe (mi pare di aver capito che viene sovrascritto dal codice:

 sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A14")

In teoria bisognerebbe fare in modo che la CurrentRegion.Copy _ si riferisca ad una destinazione variabile e che non necessariamente deve essere copiata a partire da A14. Potrebbe essere A20, A200 etc etc.

Inoltre, se possibile, tra i vari Destination:=.Range("XYZ") dovrebbe rimanerci una riga bianca, o quantomeno qualcosa che mi faccia capire dove finisce una destination range e ne inizia un'alltra. Poi eventualmente per il cartiglio posso lavorare anche a mano.

 

Grazie ancora di tutto.

Public Sub m()

    'dichiaro le variabili

    Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    Dim rng As Range

    Dim lUltRiga As Long

    Dim s1 As String

    Dim s2 As String

    'nelle due stringhe metto cosa deve trovarsi

    'come intestazione dei dati dei fogli Foglio2

    'e Foglio3 in Foglio1(modifica a tuo piacimento)

    s1 = "Titolo dei dati derivanti dal Foglio2"

    s2 = "Titolo dei dati derivanti dal Foglio3"

    'metto un riferimento ai fogli

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

    With sh1

        'trovo l'ultima riga utilizzata nel Foglio1

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'cancello il contenuto ed eventuali

        'formattazioni del Foglio1 da A3 a H(n)

        .Range("A3:H" & lUltRiga).Clear

        'inserisco in A3 l'intestazione per i dati

        'del Foglio2

        .Range("A3").Value = s1

        'copio/incollo i dati di Foglio2

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A4")

        'trovo l'ultima riga utilizata adesso in Foglio1

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'inserisco in A(n) l'intestazione per i dati

        'del Foglio3 lasciando una riga bianca

        .Range("A" & lUltRiga + 2).Value = s2

        'copio/incollo i dati del Foglio3

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A" & lUltRiga + 3)

        'eventuali formattazioni alle intestazioni

        '(commenta o elimina il codice se non ti serve)

        '***INIZIO FORMATTAZIONE***

        Set rng = .Range("A3,A" & lUltRiga + 2)

        With rng.Font

            .Size = 14

            .ColorIndex = 3

            .Bold = True

            'ecc

        End With

        '***FINE FORMATTAZIONE***

    End With

    'Set a Nothing delle variabili oggetto

    Set rng = Nothing

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

End Sub

Se(se) servono altre formattazioni in aree distinte del Foglio1, fai sapere quali e come vuoi formattarle. Grazie.

---

--

La soluzione, il codice ed i files sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dalla soluzione proposta se usata impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare la soluzione in files importanti.

--

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

Answer 2

Allego il documento a questo percorso: www.wormsmania.net/doc_miky/esempio.pdf

In base a quello che vedo nel link, se ho capito bene e se i riferimenti sono corretti, prova:

Public Sub m()

    'dichiaro le variabili

    Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    'metto un riferimento ai fogli

    '(modifica i nomi con i tuoi")

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

    With sh1

        'elimino i dati dalle celle del Foglio1 che

        'dovrebbero ricevere i dati dal

        'Foglio2(lascio eventuali formattazioni)

        .Range("A4:H12").Value = ""

        'elimino i dati dalle celle del Foglio1 che

        'dovrebbero ricevere i dati dal

        'Foglio3(lascio eventuali formattazioni)

        .Range("A14:H30").Value = ""

        'copio i dati dal Foglio2 e li incollo

        'nel Foglio1 a partire da A4

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A4")

        'copio i dati dal Foglio3 e li incollo

        'nel Foglio1 a partire da A14

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A14")

    End With

    'Set a Nothing delle variabili oggetto

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

End Sub

Non ho però capito se il Range destinato a Foglio2 e Foglio3 sul Foglio1 può cambiare, cioè se A4:H12 e A14:H30 sono fissi sul Foglio1 o se possono/devono espandersi/contrarsi.

Il codice è commentato in modo che tu possa capire cosa fanno le singole istruzioni.

---

--

La soluzione, il codice ed i files sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dalla soluzione proposta se usata impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare la soluzione in files importanti.

--

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

Answer 3

Non ho però capito se il Range destinato a Foglio2 e Foglio3 sul Foglio1 può cambiare, cioè se A4:H12 e A14:H30 sono fissi sul Foglio1 o se possono/devono espandersi/contrarsi.

     

Si, il Range destinato al Foglio 1 può cambiare cioè A4:H12 e A14:H30non sono fissi ma devono appunto espandersi/contrarsi (solo per il numero di righe però).

Ho provato tutte e due le macro: in quella di elianof, la riga 14 rimane fissa quando invece dovrebbe adattarsi alla fine del range che desume dal Foglio 2 (ma il resto è perfetto :-)), mentre nella macro di Gamberini la riga 14 viene "sovrascritta" qualora il range di dati nel foglio 2 sia superiore alle 9 righe (mi pare di aver capito che viene sovrascritto dal codice:

 sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A14")

In teoria bisognerebbe fare in modo che la CurrentRegion.Copy _ si riferisca ad una destinazione variabile e che non necessariamente deve essere copiata a partire da A14. Potrebbe essere A20, A200 etc etc.

Inoltre, se possibile, tra i vari Destination:=.Range("XYZ") dovrebbe rimanerci una riga bianca, o quantomeno qualcosa che mi faccia capire dove finisce una destination range e ne inizia un'alltra. Poi eventualmente per il cartiglio posso lavorare anche a mano.

Grazie ancora di tutto.

Answer 4

Ciao Mikele.

Mauro normalmente si rifiuta di scaricare oggetti dal Web, per cui, basandomi su quello che scrivi penso che basti effettuare poche modifiche alla sua macro, desumendole da quello che screivi. Provala così anche se non ho capito cosa c'è alla riga 13, se le righe da copiare dal Foglio2 sono sempre 9 oltre al mistero del quarto foglio.:-))

Public Sub m()

 Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    Dim lUltRiga As Long

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

    With sh1

'        .Cells.Clear    ' è commentata

        sh2.Range("A1:H9").Copy _

            Destination:=.Range("A4")

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A" & lUltRiga + 2)

    End With

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

End Sub

Facci sapere, anzi faGLI sapere

Se poi volessi procedere con formule:

Nel Foglio1, in A4:

=SE(Foglio2!A1="";"";Foglio2!A1)

da copiare a destra e in basso

Newl Foglio1, in A14 (A15 secondo Mauro):

=SE(Foglio3!A1="";"";Foglio3!A1)

da copiare a destra e in basso

Saluti

Eliano

Answer 5

Forse è meglio capire bene. Cartiglio non fa parte delle librerie di oggetti di Excel, per favore spiega(bene) cosa intendi, grazie. Tu hai davanti il tuo file, noi no. Ora Foglio2 e Foglio3 possono avere n righe, ma su quali colonne? Vuoi riportare queste righe sul Foglio1, ok? Se è così, dove riportiamo quei dati, cioè da dove partiamo(riferimento righe e colonne)? Inoltre, se abbiamo dei dati precedenti sul Foglio1, che ne facciamo? Li teniamo come se fossero una specie di storico? Li cancelliamo e li sostituiamo? Grazie per l'attenzione.

---

Confermo quanto detto da elianof. Mi scuso per l'essermi espresso male e non essermi fatto capire, ma mi rendo conto che voler essere brevi non è di aiuto.

Cerco di farti un layout veloce dei tre fogli e ti allego un pdf esemplificativo (vedi giù).

Nella realtà il mio documento da elaborare è costituito da 4 fogli (uno globale e gli altri 3 di dati). Ma a me basta capire una impostazione della macro, anche per imparare qualcosina.

Nell'immagine che allego vedrai il Foglio 1 che è quello che uso come layout di stampa, sul quale ci sono "intestazioni" (le prime 3 righe e la riga 13 ovvero le aree blu) ed ulteriori dati elaborati a destra (area gialla). Che quest'ultima area a destra sia dinamica non mi interessa... in realtà mi porto giù le formule che usoper una riga (nell'esempio per dire (I4:Q4)). Il cartiglio (immaginalo come una sorta di skin) è dunque rappresentato dall'area BLU e l'area GIALLA è un'ulteriore elaborazione che eseguo sul Foglio 1 in modo da avere un foglio unico con dati riassuntivi e globali.

L'area rossa (in questo esempio di 9 righe) prende riferimento dal foglio 2 nel range A1:H9 (le colonne sono fisse, non variano come il numero di righe)

L'area verde (14 righe) prende riferimento dal foglio 3 nel range A1:H14 (come puoi vedere le colonne sono sempre quelle 9, fino ad H).

Il problema è che i Fogli 2 e 3 raccolgono dati variabili nel tempo, in cuiil numero di righe variano , mentre le colonne rimangono sempre 9.

Ora, tornando sul Foglio 1, vorrei far in modo che le aree rossa e verde (riferite ai Fogli 2 e 3) riportino i dati dinamicamente, tenendo conto che il numero delle righe cambia con i vari aggiornamenti che io apporto.

Se fosse stato possibile definire delle finestre con il lembo inferiore dinamico, sarebbe stato tutto estremamente semplice, ma purtroppo non riesco proprio a risolvere questo problema.

Ovviamente, anche come dicevo prima, vorrei fare in modo che tutte le celle del Foglio 1 non perdano informazioni e formule con la macro che vado ad eseguire con l'istruzione .Cells.Clear

Allego il documento a questo percorso: www.wormsmania.net/doc_miky/esempio.pdf

Grazie ancora per il supporto, cordiali come sempre.