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

Grazie Mauro, veramente una pronta e stupenda risposta.

Adesso ho qualche problema col codice per adattarlo al mio cartiglio.

Se ho altri problemi sarò ancora qui.

Grazie ancore, offrite veramente un buon servizio.

Grazie per il cortese riscontro e le belle parole. Mi raccomando, se hai problemi, sii esaustivo negli esporli(nome fogli, range interessati, ecc.). Grazie e buon lavoro.

---

--

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 4

Grazie Mauro, veramente una pronta e stupenda risposta.

Adesso ho qualche problema col codice per adattarlo al mio cartiglio.

Se ho altri problemi sarò ancora qui.

Grazie ancore, offrite veramente un buon servizio.

Answer 5

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.

Vediamo se ho capito. Supponiamo che in Foglio2 e Foglio3 tu abbia due tabelle di questo tipo:

Foglio2/Dato1	Foglio2/Dato2	Foglio2/Dato3	Foglio2/Dato4
a2	b2	c2	d2
a3	b3	c3	d3
a4	b4	c4	d4
a5	b5	c5	d5
a6	b6	c6	d6

Foglio3/Dato1	Foglio3/Dato2	Foglio3/Dato3	Foglio3/Dato4
a2	b2	c2	d2
a3	b3	c3	d3
a4	b4	c4	d4

e che tu voglia riportarle una sotto l'altra in Foglio1.

• ALT+F11 e sei nell'editor del vb di Excel
• Inserisci-->Modulo
• Copia/incolla questa routine nel foglio bianco:

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

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

            Destination:=.Range("A1")

        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

• ALT+F11 e sei di nuovo in Excel lato celle.
• ALT+F8 e attivi la finestra: Macro
• Seleziona la macro m
• Pulsante: Opzioni
• Assegna un tasto di scelta rapido alla macro(esempio: CTRL+a)
• Ok
• Chiudi la finestra Macro

Adesso tutte le volte che vuoi il riepilogo dei due fogli in Foglio1, non devi fare altro che premere CTRL+a. Prova un po' a modificare le due tabelle in Foglio2 e Foglio3 e premere CTRL+a. Questo è solo un esempio e va adattato al tuo contesto(nome dei fogli, range delle tabelle). Vedi un po' se riesci a risolvere. Resta sempre in questo thread per eventuali problemi. Grazie per l'attenzione.

---

--

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/